January 9, 2014 at 10:45 pm
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?
January 10, 2014 at 7:25 am
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
January 12, 2014 at 5:37 pm
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 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
January 16, 2014 at 12:16 am
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).
January 16, 2014 at 4:38 am
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 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