simple case question - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

simple case question

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-02-17 : 11:44:03

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-02-17 : 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 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?

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-02-17 : 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

52326 Posts

Posted - 2014-02-18 : 13:30:29

[code]
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]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-02-19 : 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. 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources