Post reply

t-sql help

  • March 30, 2009 at 4:12 pm

    #384250

    I have the following sample data:

    Ord_refil1 ord_refil2 ord_refil3 online_ord1 online_ord2 online_ord3 loc

    med1 med2med3 01 0 loc1

    med1 nullnull 01 0 loc2

    null med2med3 00 0 loc1

    null med2null 10 0 loc3

    null med2null 00 0 loc4

    I have order_refill columns from Order_refill1,Order_refill2,Order_refill3......Order_refill15.

    In the similar way I have online_order1,online_order2,online_order3.....online_order15.

    In the above data: the values for online_orders mean: 0 is non-onlineorder; 1 is onlineorder

    I need to get the following output from the above data, should display the counts of online_orders and non-onlineorders for each location based on order_refills, we should count only where order_refill column is not null

    LocationOnline_OrderNon-OnlineOrder

    loc114

    loc210

    loc310

    loc401

    Thanks for all your help.

  • March 30, 2009 at 4:32 pm

    #968774

    aw comeon Mh;

    with 170+posts, I KNOW you know better.

    Always post a table structure and insert statements so people can help you.

    here's a fake table and data for others to try to use...

    CREATE TABLE #blah(

    Ord_refil1 varchar(30),

    ord_refil2 varchar(30),

    ord_refil3 varchar(30),

    online_ord1 int,

    online_ord2 int,

    online_ord3 int,

    loc varchar(30) )

    INSERT INTO #blah

    SELECT 'med1','med2','med3',0,1,0,'loc1' UNION ALL

    SELECT 'med1', null,null,0,1,0,'loc2' UNION ALL

    SELECT null,'med2','med3',0,0,0,'loc1' UNION ALL

    SELECT null,'med2',null,1,0,0,'loc3' UNION ALL

    SELECT null,'med2',null,0,0,0,'loc4'

    select * from #blah

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 30, 2009 at 6:04 pm

    #968812

    Thanks Lowell for the table creates.

    Mh is this what you are after? If this is not right you may need to provide further explanation.

    with cte(loc, online_order, non_onlineOrder)

    as (

    select loc, case when ord_refil1 is not null AND online_ord1=1 then 1

    when ord_refil2 is not null AND online_ord2=1 then 1

    when ord_refil3 is not null AND online_ord3=1 then 1 else 0 end as online_Order,

    case when ord_refil1 is not null AND online_ord1=0 then 1

    when ord_refil2 is not null AND online_ord2=0 then 1

    when ord_refil3 is not null AND online_ord3=0 then 1 else 0 end as non_onlineOrder

    from #blah

    )

    select loc, sum(online_order) as online_order, sum(non_onlineOrder) as non_onlineOrder

    from cte

    group by loc

    Bevan

  • March 31, 2009 at 10:09 am

    #969252

    Actually I should get the following output(0 is non-onlineorder; 1 is onlineorder):

    LocationOnline_OrderNon-OnlineOrder

    loc114

    loc210

    loc310

    loc401

    Like I said before, I have 15 columns of order_refill i.e Order_refill1,Order_refill2,Order_refill3......Order_refill15.

    In the similar way I have 15 columns of online_order's i.e online_order1,online_order2,online_order3.....online_order15.

    Please let me know. Thanks.

  • March 31, 2009 at 1:55 pm

    #969419

    Hi Mh,

    Is the logic wrong somewhere? If so, could you please explain further.

    Or is it just that this query is not showing all 15 columns?

Viewing 5 posts - 1 through 4 (of 4 total)

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