Post reply

CASE HELP

  • November 30, 2011 at 9:15 am

    #390998

    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_STATUS ORDER_NUMBER UNIT_NUMBER

    50 3848 004

    55 3848 005

    65 3848 006

    40 3848 015

    40 3848 016

    40 3848 017

    70 3848 018

  • November 30, 2011 at 9:21 am

    #1414765

    Whats your expected output for the sample data you've given ?

  • November 30, 2011 at 9:27 am

    #1414771

    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

    #1414783

    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

    #1414799

    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

    #1414802

    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

    #1414846

    Drew,

    Thank you much.. the second sample did it..

  • November 30, 2011 at 5:29 pm

    #1414990

    Thank you!!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply