SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 simple case question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 Posts

Posted - 02/17/2014 :  11:44:03  Show Profile  Reply with Quote
I have 4 fields. job-no, price, qty-shipped, qty-orig-order. 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 qty-orig-order = -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 qty-orig-order they should subtract each other to 0.

Total Billed is sum price when qty-shipped = 1 and not sure how to write if qty-orig-order = -1 subtract matching price.... (really confused.) example 454204 has 6 lines but only 3 have qty-shipped only sum those lines.

Example data

job-no, price, qty-shipped, qty-orig-order
600388, 7801.000, 1, 1
600388, 7801.000, 0, -1
600388, 7506.000, 1, 1


Result I am looking for.

job-no, Total Value, Total Billed
600388 7506.000 7506.00


-----------------------------------------------------------------

job-no, price, qty-shipped, qty-orig-order
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.

job-no, 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  Show Profile  Reply with Quote
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 qty-shipped only sum those lines.

If we take out the value that is associated with a -1 qty-orig-order 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?
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 02/17/2014 :  15:18:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2014 :  13:30:29  Show Profile  Reply with Quote

SELECT [job-no],
SUM(CASE WHEN [qty-orig-order] = -1 THEN -1 * price ELSE 1 * price END) AS TotalValue,
SUM(CASE WHEN [qty-shipped]=0 THEN 0 WHEN [qty-orig-order] = -1 THEN -1 * price ELSE 1 * price END) AS TotalBilled
FROM Table
GROUP BY [job-no]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 02/19/2014 :  07:58:56  Show Profile  Reply with Quote
Thank you Visakh16 wonderful as usual. I did had to make a minor change since their were variables that could change the outcome. QTY-orig-ord will always have a 1 or a -1 but shipped could have a 0 or a 1. so the combination of qty-orig-ord 1 or -1 with qty-shipped being 0 or -1 the Total bill is the only thing needing the OR statement. Also thank you Lamprey.

SELECT[Job-no],
SUM(CASE WHEN [qty-orig-ord] = - 1 THEN - 1 * price ELSE 1 * price END) AS TotalValue,
SUM(CASE WHEN [qty-orig-ord] = 1 AND[qty-shipped] = 0 THEN 0 WHEN [qty-orig-ord] = - 1 OR [qty-orig-ord] = - 1 AND [qty-shipped] = 0 THEN - 1 * price ELSE 1 * price END) AS TotalBilled
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000