Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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  
 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.13 seconds. Powered By: Snitz Forums 2000