Filtering an Aggregate - 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

General SQL Server Forums

New to SQL Server Programming

Filtering an Aggregate

Author

Topic

AdenJones
Starting Member

17 Posts

Posted - 2009-06-09 : 02:18:25

The following as a somewhat monolithic query which Iam probably trying to get to do too many things at the same time. What I was hoping to do was to get the MAX of a date value filtered by another value.

The Line: MAX(CASE WHEN tblReflectionNote.OutreachID > 0 THEN tblReflectionNote.RecordDate END) AS LastOutreach

Is where Iam attempting to do this.

What it is doing is ignoring some values and including others but not based upon the logic Iam feeding it.

SELECT tblMember.MemberID,
		tblMember.Surname,
		tblMember.FirstName,
		tblOutreachMethod.OutreachMethod,
		tblMember.MemberStatus,
		MAX(CASE WHEN tblReflectionNote.OutreachID > 0 THEN tblReflectionNote.RecordDate END) AS LastOutreach
FROM tblMember
LEFT OUTER JOIN tblReflectionNote ON tblMember.MemberID = tblReflectionNote.MemberID 
LEFT OUTER JOIN tblOutreachMethod ON tblReflectionNote.OutreachID = tblOutreachMethod.OutreachMethodID
WHERE tblMember.Outreach = 1
AND tblMember.MemberStatus IN('Member','WaitList')
AND tblMember.MemberID NOT IN(SELECT tblReflectionNote.MemberID
								FROM tblReflectionNote 
								LEFT OUTER JOIN tblMember ON tblMember.MemberID = tblReflectionNote.MemberID 
								LEFT OUTER JOIN tblTimeUnit ON tblMember.OutreachTimeUnitID = tblTimeUnit.TimeUnitID
								WHERE tblReflectionNote.OutreachID > 0
								AND (OutreachTimeUnitID IS NULL OR OutreachTimeUnitID != 5)
								AND RecordDate > 
									CASE WHEN OutreachTimeUnitID IS NULL
									THEN DATEADD(month,-2,GETDATE())
									ELSE
										CASE tblTimeUnit.TimeUnitIs 
										WHEN 1 THEN DATEADD(day, -TimeUnitValue, GETDATE())
										WHEN 2 THEN DATEADD(month, -TimeUnitValue, GETDATE())
										END
									END)
AND tblMember.MemberID NOT IN(SELECT tblAttendance.MemberID
					FROM tblAttendance
					INNER JOIN tblMember ON tblMember.MemberID = tblAttendance.MemberID 
					INNER JOIN tblTimeUnit ON tblMember.OutreachTimeUnitID = tblTimeUnit.TimeUnitID
					AND DateAtt > CASE tblTimeUnit.TimeUnitIs WHEN 1 THEN DATEADD(day, -TimeUnitValue, GETDATE())
					WHEN 2 THEN DATEADD(month, -TimeUnitValue, GETDATE()) END)
GROUP BY tblMember.MemberID,
		tblMember.Surname,
		tblMember.FirstName,
		tblOutreachMethod.OutreachMethod,
		tblMember.MemberStatus
ORDER BY LastOutreach

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-09 : 02:26:00

this ?


HAVING MAX(CASE WHEN tblMember.OutreachTimeUnitID > 0 THEN tblReflectionNote.RecordDate END) = @anothervalue

KH
[spoiler]Time is always against us[/spoiler]

AdenJones
Starting Member

17 Posts

Posted - 2009-06-09 : 02:33:40

Iam not trying to filter the entire recordset Iam just trying to filter the values that are passed to the MAX() aggregate.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 03:14:35

You are!
Your MAX function only deals with RecordDates where OutreachID is bigger than zero.

E 12°55'05.63"
N 56°04'39.26"

AdenJones
Starting Member

17 Posts

Posted - 2009-06-09 : 03:42:50

The query logic is as follows.

Return all records other than those filtered by the IN's
For the records that are returned show the MAX(date) for ReflectionNotes that are not OutreachID 0 (for the others Iam happy to just return null)

So I don't want to filter the outer result set only the values that are returned by MAX()

Hope that clarifies.

AdenJones
Starting Member

17 Posts

Posted - 2009-06-09 : 03:45:44

When I tested the results Some records were showing a null value for the MAX() aggregate when they did have dates that were of outreachID > 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-09 : 21:41:10

quote:
Originally posted by AdenJones

When I tested the results Some records were showing a null value for the MAX() aggregate when they did have dates that were of outreachID > 0

can you post your table DDL and some sample data of this scenario ?
Also include the expected result from these sample data

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources