Conditional query to return variable from table

Question

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

If any of the rows associated with O.OrderId contain %FINISHED% in DisplayString OR OrderStatus = 1; then return 1
If any of the rows associated with O.OrderId contain OrderStatus = 2 AND NOT %FINISHED% in DisplayString; then return 2
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

manurajhada · Accepted Answer · 2012-07-05 07:54:11Z

1

User case statement as per the taste of your sql.

answered Jul 5, 2012 at 7:54

manurajhada

5,4004 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatBailie Over a year ago

Could you show the OP an example? As it is both CASE and an aggregated query.

dadord Over a year ago

An example would be really helpful!

Void Ray · Accepted Answer · 2012-07-05 15:42:21Z

0

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

answered Jul 5, 2012 at 15:42

Void Ray

10.3k4 gold badges36 silver badges55 bronze badges

Collectives™ on Stack Overflow

Conditional query to return variable from table

Order Table

OrderHistory Table

Conditions

Result

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Order Table

OrderHistory Table

Conditions

Result

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related