November 30, 2011 at 9:15 am
I have a table that contains items in the pipeline that are being manufacture at different times but in a batch or order#. I need to build a query that will identify those orders or batches that are partially done using a case stmt adding a column (ORDER_STATUS) , the way that they are identified is by statusid. see sample data. Any help is greatly appreciated..
40 = completed GT 40 = being manufactured or not ready
ITEM_STATUSORDER_NUMBERUNIT_NUMBER
50 3848004
55 3848005
65 3848006
40 3848015
40 3848016
40 3848017
70 3848018
November 30, 2011 at 9:21 am
Whats your expected output for the sample data you've given ?
November 30, 2011 at 9:27 am
Just another column 'ORDER_STATUS' = 'PARTIAL'
ITEM_STATUS ORDER_NUMBER UNIT_NUMBER ORDER_STATUS
50 3848 004 PARTIAL
55 3848 005 PARTIAL
65 3848 006 PARTIAL
40 3848 015 PARTIAL
40 3848 016 PARTIAL
40 3848 017 PARTIAL
70 3848 018 PARTIAL
40 3849 001 COMPLETED
40 3849 002 COMPLETED
40 3849 003 COMPLETED
November 30, 2011 at 9:36 am
the new sample data confuses me. For 3849 with item_status = 40 , the order_status is "completed" as per ur data, but for 3848 it is Partial. can u pls throw some light on how to determine the order_status ?
November 30, 2011 at 9:57 am
It looks like you're going to need to use an aggregate function with windowing. The simplest case would be
CASE WHEN MAX(Status_ID) OVER( PARTITION BY Order_Number ) > 40 THEN 'Partial' ELSE 'Complete' END
This may not work if there isn't a simple mapping of status_id to complete/partial, so you may have to change the scope of the aggregate.
Max(CASE WHEN Status_ID IN ('55', '65) THEN 'Partial' ELSE 'Complete' END) OVER( PARTITION BY Order_Number )
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 30, 2011 at 9:59 am
Sorry to confuse you, I trew that extra order that have only 3 items and were completed.
As you see the last column ORDER_STATUS, that is the column I need help with the CASE function.
November 30, 2011 at 10:39 am
Drew,
Thank you much.. the second sample did it..
November 30, 2011 at 5:29 pm
Thank you!!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply