How to get net sales from these table?

  • Hi Friends,

    I ve the table like

    Create table nh

    (

    invoice_no varchar(20),

    model_ref varchar(20),

    item_no int,

    item_type varchar(20),

    invoice_qty int

    inv_date datetime

    )

    insert into nh(invoice_no,model_ref,item_no,item_type,invoice_qty,inv_date)

    values('tn/002/13-14','#','600032','K','150','01-04-2013')

    insert into nh(invoice_no,model_ref,item_no,item_type,invoice_qty,inv_date)

    values('tn/002/13-14','600032','5000399','X','180','01-04-2013')

    in above these example

    invoice_no='tn/002/13-14' type='K' means kit it holds some item value with free.

    Model_ref is item_no referred to that KIT (I.E ITEM_TYPE='X') Holds some original value

    now my expecting o/p:

    invoice_no item_no sales free

    tn/002/13-14 5000399 150 30

    (i.e kit value has been taken sales model_ref item value total value - kit value=free)

    how to that?

  • What code do you have so far? Maybe I can assist your issues.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • This query will get you the output you requested but I'm sure it is way over-simplified.

    SELECT invoice_no

    ,item_no=MAX(CASE WHEN item_type <> 'K' THEN item_no END)

    ,sales=SUM(CASE WHEN item_type = 'K' THEN invoice_qty END)

    ,free=SUM(CASE WHEN item_type <> 'K' THEN invoice_qty END)

    -SUM(CASE WHEN item_type = 'K' THEN invoice_qty END)

    FROM nh

    GROUP BY invoice_no;

    For example, what happens when you have an invoice with multiple kits?


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Hi dwain.c,

    Thanks for ur reply.....

    i need the o/p like each item wise (i.e you wrote the code for max item ,

    now each item should be display with free and net sales how to do).

  • raghuldrag (1/16/2014)


    Hi dwain.c,

    Thanks for ur reply.....

    i need the o/p like each item wise (i.e you wrote the code for max item ,

    now each item should be display with free and net sales how to do).

    Not sure if I understand you but my code produces precisely the output you originally asked for. If it does not get what you want, try comparing what it produces with what you want (i.e., show both results). Possibly providing more sample data (yours was pretty sparse) might help to elucidate the problem.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

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

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