Author 
Topic 

hbadministrator
Posting Yak Master
118 Posts 
Posted  02/17/2014 : 11:44:03

I have 4 fields. jobno, price, qtyshipped, qtyorigorder. I am trying to write a case when for 2 new fields. Total Value then total billed.
Total value is Sum of Price  price value if qtyorigorder = 1. example is the 600388 that total should only be 7506.00 since the 7801.00 has a 1 as well as a 1 in the qtyorigorder they should subtract each other to 0.
Total Billed is sum price when qtyshipped = 1 and not sure how to write if qtyorigorder = 1 subtract matching price.... (really confused.) example 454204 has 6 lines but only 3 have qtyshipped only sum those lines.
Example data
jobno, price, qtyshipped, qtyorigorder 600388, 7801.000, 1, 1 600388, 7801.000, 0, 1 600388, 7506.000, 1, 1
Result I am looking for.
jobno, Total Value, Total Billed 600388 7506.000 7506.00

jobno, price, qtyshipped, qtyorigorder 454204, 2540.000, 1, 1 454204, 2540.000, 0, 1 454204, 2540.000, 1, 1 454204, 3943.000, 1, 1 454204, 467.950 , 0, 1 454204, 4235.950, 1, 1
Result I am looking for.
jobno, Total Value, Total Billed 454204 11186.90 10718.00 
Edited by  hbadministrator on 02/17/2014 15:21:46


Lamprey
Flowing Fount of Yak Knowledge
4614 Posts 
Posted  02/17/2014 : 12:55:12

I don't think I understand how you get the Total Billed for 454204. Are the "Result I am looking for" correct?
You said: example 454204 has 6 lines but only 3 have qtyshipped only sum those lines.
If we take out the value that is associated with a 1 qtyorigorder That leave us:
454204, 2540.000, 1, 1 454204, 3943.000, 1, 1 454204, 4235.950, 1, 1
Summing those together gets a much larger number than your expected results.
So, should the result be a larger number (sum of those three rows) or is the logic description incorrect? 


hbadministrator
Posting Yak Master
118 Posts 
Posted  02/17/2014 : 15:18:54

It would be these 4. Total Value 454204, 2540.000, 1, 1 454204, 3943.000, 1, 1 454204, 467.950 , 0, 1 454204, 4235.950, 1, 1 Total Value 11186.90
Total Billed would be just those 3 454204, 2540.000, 1, 1 454204, 3943.000, 1, 1 454204, 4235.950, 1, 1 Total Billed 10718



visakh16
Very Important crosS Applying yaK Herder
India
52317 Posts 
Posted  02/18/2014 : 13:30:29

SELECT [jobno],
SUM(CASE WHEN [qtyorigorder] = 1 THEN 1 * price ELSE 1 * price END) AS TotalValue,
SUM(CASE WHEN [qtyshipped]=0 THEN 0 WHEN [qtyorigorder] = 1 THEN 1 * price ELSE 1 * price END) AS TotalBilled
FROM Table
GROUP BY [jobno]
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



hbadministrator
Posting Yak Master
118 Posts 
Posted  02/19/2014 : 07:58:56

Thank you Visakh16 wonderful as usual. I did had to make a minor change since their were variables that could change the outcome. QTYorigord will always have a 1 or a 1 but shipped could have a 0 or a 1. so the combination of qtyorigord 1 or 1 with qtyshipped being 0 or 1 the Total bill is the only thing needing the OR statement. Also thank you Lamprey.
SELECT[Jobno], SUM(CASE WHEN [qtyorigord] =  1 THEN  1 * price ELSE 1 * price END) AS TotalValue, SUM(CASE WHEN [qtyorigord] = 1 AND[qtyshipped] = 0 THEN 0 WHEN [qtyorigord] =  1 OR [qtyorigord] =  1 AND [qtyshipped] = 0 THEN  1 * price ELSE 1 * price END) AS TotalBilled 



Topic 


