CODEWITHSUNDEEP
sqlsql-server
dadord
dadord

Reputation: 71

Conditional query to return variable from table

Im trying to do some conditional queries on Azure SQL, but Im totally lost on how to do it. I have these two tables:

Order Table

OrderID (PK)
...

OrderHistory Table

OrderHistoryId (PK)
OrderId (FK)
DisplayString
OrderStatus

Now what I want to do is join the table OrderHistory to my query, and return a variable based on some conditional queries against OrderHistory

SELECT O.OrderId, [...], Variable
FROM [Order] AS O

-- some code to get "Variable" from OrderHistory

ORDER BY O.OrderId DESC

OFFSET 0 ROWS
FETCH NEXT 200 ROWS ONLY

Conditions

  1. If any of the rows associated with O.OrderId contain %FINISHED% in DisplayString OR OrderStatus = 1; then return 1
  2. If any of the rows associated with O.OrderId contain OrderStatus = 2 AND NOT %FINISHED% in DisplayString; then return 2
  3. If the SUM of all OrderStatus associated with O.OrderId is equal to 0; then return 3

Result

Here's what I want as a result:

OrderId    [...]    Variable
1           ...     1
2           ...     3
3           ...     2
4           ...     2

Upvotes: 1

Views: 107

Answers (2)

Void Ray
Void Ray

Reputation: 10209

This query might need some more work, but it should demonstrate the "case":

select Variable = case when b.DisplayString like 'FINISHED%' or b.OrderStatus = 1
                       then 1
                       else case when b.DisplayString not like 'FINISHED%' and b.OrderStatus = 2
                            then 2
                            else case when c.sumOsStatus = 0 then 3
                            end
                       end
                  end,
* 
from [Order] a
inner join OrderHistory b
    on a.OrderId = b.OrderId
inner join (select OrderId, sum(OrderStatus) sumOsStatus from OrderHistory group by OrderId) c
    on a.OrderId = c.OrderId

Upvotes: 0

manurajhada
manurajhada

Reputation: 5380

User case statement as per the taste of your sql.

Upvotes: 1

Related Questions