SUM of Max Unique records - 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)

SUM of Max Unique records

Author

Topic

sunegtheoverlord
Starting Member

5 Posts

Posted - 2010-12-17 : 07:00:09

Hi everyone,

I have a table "BoardPoints" with columns InputRef, WorkingDay, ConsultantRef and Enquiries.

I need to pull off a SUM of the Maximum values of Enquiries for each Consultant in any given month.

I can do this easily for one consultant with:-

						
SELECT MAX(Enquiries) AS Enquiries
FROM BoardPoints
WHERE ConsultantRef = @consultantref
AND WorkingDay BETWEEN  (
			SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) 	)							AND	(
			SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
			)

But i need the code to add all of the max Enquiries values for every different consultant, so say BoardPoints contains these records:-

InputRef WorkingDay ConsultantRef Enquiries
1638 2010-11-16 00:00:00.000 541000005 70
1640 2010-11-16 00:00:00.000 541000020 86
1637 2010-11-16 00:00:00.000 541000019 54
1639 2010-11-16 00:00:00.000 541000018 0
1633 2010-11-15 00:00:00.000 541000019 44
1634 2010-11-15 00:00:00.000 541000005 70
1635 2010-11-15 00:00:00.000 541000018 0
1636 2010-11-15 00:00:00.000 541000020 82
1629 2010-11-14 00:00:00.000 541000019 44
1631 2010-11-14 00:00:00.000 541000018 0

I need the query to return 70 + 86 + 54 + 0, so 210

Any help would be appreciated.

S

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-17 : 07:07:59

SELECT SUM(Yak) FROM
(
SELECT ConsultantRef, MAX(Enquiries) AS Yak FROM BoardPoints
WHERE WorkingDay BETWEEN ... AND ...
GROUP BY ConsultantRef
) AS d

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

sunegtheoverlord
Starting Member

5 Posts

Posted - 2010-12-17 : 08:23:24

Thanks Peso!

perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources