Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get net sales from these table? Expand / Collapse
Author
Message
Posted Thursday, January 9, 2014 10:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:57 AM
Points: 179, Visits: 448
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?


Post #1529640
Posted Friday, January 10, 2014 7:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 11:09 AM
Points: 989, Visits: 1,329
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
Post #1529792
Posted Sunday, January 12, 2014 5:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1530165
Posted Thursday, January 16, 2014 12:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:57 AM
Points: 179, Visits: 448
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).
Post #1531419
Posted Thursday, January 16, 2014 4:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1531491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse