November 27, 2014 at 5:18 pm
I have a requirement to present computed sales order status value
Parent table : consumer
Child table of consumer : sales order
Child table of sales order: items
Items table has status column with awaiting, accepted , rejected.
Status of the sales order should be set as following
If all items are rejected then sales order status=reject
Or if at least one item is accepted then sales order status = accept
Or if items status is awaiting then sales order status = pend
Please provide query to find all sales order per consumer with computed order status for each sales order.
November 28, 2014 at 12:48 am
Hi Saujib,
To get a quicker, more accurate response, can you please supply the table structures and some sample data. The first link in my signature block shows a good way to do this.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 28, 2014 at 2:24 am
Encode values according to priority, max-aggregate priority, and decode it. Something like this
select orderID
,case max(case Items.status
when 'rejected' then 1
when 'accepted' then 2
when 'awaiting' then 3
end)
when 1 then 'reject'
when 2 then 'accept'
when 3 then 'pend'
end as orderStatus
from Items
group by orderID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply