selecting based on the value of a SUM

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

General SQL Server Forums

New to SQL Server Programming

selecting based on the value of a SUM

Author

Topic

petenuffer
Starting Member

6 Posts

Posted - 2008-04-21 : 15:14:28

Okay, let's see if I can explain this one. I am summing multiple lines of data from a labor detail table, by status. Using this query

SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor, SUM(LD.WrittenOff) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1

I get these results...

Lastname WBS1 WBS2 Longname HELDLABOR TBWrittenOffLabor WRITTENOFFLABOR
Boulet 0001039.00 0100 S.r. 41 & Del Prado Shopping Center/miscellaneous civil engineering 18408.63 0 9923.47
Boulet 0001039.00 0102 S.r. 41 & Del Prado Shopping Center/rezoning process 0 0 8790
Boulet 0001039.00 0106 S. R. 41 & Del Prado Shopping center / const plan rev for environ planting 2200.68 0 0
Boulet 0001039.00 0107 S.r. 41 & Del Prado Shopping Center/cpd rezoning 9335.46 0 0

Okay, so now, of coarse, I want to change everything. I only want to return rows if there is a value > 0 in either Held Labor or TBWrittenOffLabor. Otherwise, no row return.

Here's what I tried, but it didn't work out because it still returns rows, it just zero's out the values for written off labor.

SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor,
SUM(CASE WHEN LD.HELD > '0' THEN LD.WrittenOff ELSE '0' END) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-21 : 15:15:49

Add your new conditions to the WHERE clause, that's where you filter your data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 15:21:03

Or put the condition on the HAVING clause.

E 12°55'05.25"
N 56°04'39.16"

petenuffer
Starting Member

6 Posts

Posted - 2008-04-21 : 15:27:03

Sorry, should have explain better -
a line item will contain one entry with either H, W, or X -
if I exlude the X from the where statement, then I get a zero value in the WrittenOffLabor.

I'm thinking I need a case when statement, somehting like this in my where line, but I'm not sure how to structure it...

FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w') AND CASE When Held > '0'
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-21 : 15:33:28

Perhaps this:


SELECT *
FROM
(
	SELECT 
		EM.Lastname, 
		LD.WBS1, 
		LD.WBS2, 
		P.Longname, 
		SUM(LD.Held) AS HELDLABOR, 
		SUM(LD.TBWRittenOff) as TBWrittenOffLabor, 
		SUM(LD.WrittenOff) AS WRITTENOFFLABOR
	FROM PR P 
	INNER JOIN
	(
		SELECT 
			WBS1, 
			WBS2, 
			SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, 
			SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
			SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
		FROM LD
		WHERE BillSTatus IN ('x','h', 'w')
		GROUP BY WBS1, WBS2
	) LD 
	ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 
	INNER JOIN EM 
	ON p.ProjMgr = EM.Employee
	WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
	GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
) t
WHERE HELDLABOR > 0 AND TBWrittenOffLabor > 0

It's best to format your code better when posting so that it is easy for us to read. I had to spend a few minutes in notepad formatting what you posted so that I could figure out what was going on. Use code tags in your posts to retain the format.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

petenuffer
Starting Member

6 Posts

Posted - 2008-04-22 : 11:18:47

Doh, sorry about the bad format. I've been staring at this for the last few hours....
I'm getting no results, but I think I can hammer it out - thank you for the help.

This concept of embedded select statements is very cool, but I'm (obviously) very green when it comes to this - do you have any resources that might get into it a little further? This seems like it's an embedded select statement inside an embedded select statement?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 12:42:42

They are called derived tables. I don't have any articles to provide to you to help you better understand them. You can probably pick up a good SQL book for more information.

I didn't do anything to your code except surround it like this:

SELECT *
FROM
(
YourCodeGoesHere
) t
WHERE HELDLABOR > 0 AND TBWrittenOffLabor > 0

By making YourCodeGoesHere a derived table, we are now able to use the new column names (HELDLABOR and TBWrittenOffLabor). Try changing it to an OR to see if you get rows back.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 13:31:42

Or i guess this will be enough too:-

SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as
TBWrittenOffLabor, SUM(LD.WrittenOff) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD 
ON p.WBS1 = ld.wbs1 
AND P.WBS2 = LD.WBS2
AND (LD.Held>0 OR LD.TBWrittenOff>0) 
INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources