Please help with this query - 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 2000 Forums

Transact-SQL (2000)

Please help with this query

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-09-13 : 03:52:56

HI everyone

Please see if you can find what I'm doing wrong in this query:
SELECT Locations.LocationPointer, isnull(SUM(CASE WHEN containers.containerstatus = 1 THEN 1 END), '0') AS TotalContainersIn,
isnull(SUM(CASE WHEN containers.containerstatus = 7 THEN 1 END), '0') AS TotalContainersOut,
SUM(locations.maxcap - (SUM(CASE WHEN containers.containerstatus = 1 THEN 1 END)) AS AvailableCap
FROM Locations LEFT OUTER JOIN
Containers ON Locations.LocationID = Containers.LocationID
GROUP BY Locations.LocationPointer

If I remove the SUM(locations.maxcap - (SUM(CASE WHEN containers.containerstatus = 1 THEN 1 END)) AS AvailableCap part of the query, it runs fine - so the problem lies with this sentence. If I say (sum(locations.maxcap - 2) then I also works. I'm trying to calculate the amount of containers with a containerstatus = 1 and then substract it from locations.maxcap.

Thanks

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-09-13 : 04:07:57

At a glance:

SELECT Locations.LocationPointer, isnull(SUM(CASE WHEN containers.containerstatus = 1 THEN 1 END), '0') AS TotalContainersIn, 
isnull(SUM(CASE WHEN containers.containerstatus = 7 THEN 1 END), '0') AS TotalContainersOut, 
SUM(locations.maxcap) - (SUM(CASE WHEN containers.containerstatus = 1 THEN 1 END)) AS AvailableCap
FROM Locations LEFT OUTER JOIN
Containers ON Locations.LocationID = Containers.LocationID
GROUP BY Locations.LocationPointer

Mark

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-09-13 : 04:11:12

I feel like kicking myself! Thanks man.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 04:25:49

Or use a derived table

SELECT	LocationPointer,
	TotalContainersIn
	TotalContainersOut,
	MaxCap,
	MaxCap - TotalContainersIn AS AvailableCap
FROM	(
		SELECT		loc.LocationPointer,
				SUM(CASE WHEN con.ContainerStatus = 1 THEN 1 ELSE 0 END) AS TotalContainersIn, 
				SUM(CASE WHEN con.ContainerStatus = 7 THEN 1 ELSE 0 END) AS TotalContainersOut,
				SUM(loc.MaxCap) AS MaxCap
		FROM		Locations AS loc
		LEFT JOIN	Containers AS con ON con.LocationID = loc.LocationID
		GROUP BY	loc.LocationPointer
	) AS u

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources