Query not working - 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 2005 Forums

Transact-SQL (2005)

Query not working

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2010-10-21 : 12:20:18

Here is the basic query. Perhaps This is something I can't do. I'll give you more information if there is no reason i can't do it.

I don't want to go through all the changes of providing you table info etc if this can't be done. I've never done it before at least.

I have 4 columns I'm trying to aggregate (according to Grade level) with self joins. the only one that works is the MMR one. I get 0s for the other columns. But when i try the different codes separately, i can get them to display numbers.

Its really a simple query. It just has numerious counts being done.

Thanks

SELECT g.Name AS Grade, COUNT(i1.ImmunizationTypeID) AS MMR, COUNT(i2.ImmunizationTypeID) AS DTP, COUNT(i3.ImmunizationTypeID) AS Polio, COUNT(i4.ImmunizationTypeID) AS Varicella
FROM dbo.StudentImmunization si
LEFT JOIN dbo.ImmunizationType i1 ON i1.ImmunizationTypeID = si.ImmunizationTypeID AND i1.ImmunizationTypeID = 3
LEFT JOIN dbo.ImmunizationType i2 ON i2.ImmunizationTypeID = si.ImmunizationTypeID AND i1.ImmunizationTypeID = 10
LEFT JOIN dbo.ImmunizationType i3 ON i3.ImmunizationTypeID = si.ImmunizationTypeID AND i1.ImmunizationTypeID = 11
LEFT JOIN dbo.ImmunizationType i4 ON i4.ImmunizationTypeID = si.ImmunizationTypeID AND i1.ImmunizationTypeID = 16
JOIN dbo.StudentAcadSession sas ON si.StudentID = sas.StudentID
JOIN dbo.GradeLevel g ON g.GradeLevelID = sas.GradeLevelID
WHERE sas.AcadSessionID IN (3,4,5) AND sas.CurrentEnrollmentStatus = 'C'
GROUP BY g.Name
ORDER BY g.Name

Sachin.Nand

2937 Posts

Posted - 2010-10-21 : 12:31:42

quote:
I don't want to go through all the changes of providing you table info etc if this can't be done. I've never done it before at least.

Wow...

PBUH

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-10-21 : 13:03:22

[code]
;WITH Immunization
AS
(
SELECT ImmunizationTypeID
,SUM(CASE WHEN ImmunizationTypeID = 3 THEN 1 ELSE 0 END) AS MMR
,SUM(CASE WHEN ImmunizationTypeID = 10 THEN 1 ELSE 0 END) AS DTP
,SUM(CASE WHEN ImmunizationTypeID = 11 THEN 1 ELSE 0 END) AS Polio
,SUM(CASE WHEN ImmunizationTypeID = 16 THEN 1 ELSE 0 END) AS Varicella
FROM dbo.ImmunizationType
GROUP BY ImmunizationTypeID
)
SELECT g.Name AS Grade
,SUM(COALESCE(I.MMR, 0)) AS MMR
,SUM(COALESCE(I.DTP, 0)) AS DTP
,SUM(COALESCE(I.Polio, 0)) AS Polio
,SUM(COALESCE(I.Varicella, 0)) AS Varicella
FROM dbo.GradeLevel g
JOIN dbo.StudentAcadSession sas
ON g.GradeLevelID = sas.GradeLevelID
JOIN dbo.StudentImmunization si
ON sas.StudentID = si.StudentID
LEFT JOIN Immunization I
ON si.ImmunizationTypeID = I.ImmunizationTypeID
GROUP BY g.Name
ORDER BY Grade
[/code]

JacobPressures
Posting Yak Master

112 Posts

Posted - 2010-10-21 : 14:23:59

Thanks guys! I thought about the CTE. So that was the only way to get around this issue. Is there a rule or explanation as to why?

Thanks!

JacobPressures
Posting Yak Master

112 Posts

Posted - 2010-10-22 : 10:13:18

Oh well. Thanks! I'm going to have to find a way to understand why something doesn't work. If anyone has any books or suggestions let me know. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources