SQL Display records in a group with based on ID and date filter

I have this query which gives me the results I want - The department name showing once with the total votes

SELECT DISTINCT Main_Department.main_dept AS ASTNAME, SUM(leadervotes.votelevel) AS TotalVotes, Main_Department.ID
FROM         leadervotes INNER JOIN
                      Main_Department ON leadervotes.astdept = Main_Department.ID
GROUP BY Main_Department.main_dept, Main_Department.ID


I need to filter by date - when I add the date I also lose the grouping so the department name shows several times

SELECT DISTINCT Main_Department.main_dept AS ASTNAME, SUM(leadervotes.votelevel) AS TotalVotes, Main_Department.ID, leadervotes.votedate
FROM         leadervotes INNER JOIN
                      Main_Department ON leadervotes.astdept = Main_Department.ID
GROUP BY Main_Department.main_dept, Main_Department.ID, leadervotes.votedate
HAVING      (leadervotes.votedate > CONVERT(DATETIME, '2013-08-01 00:00:00', 102))

Is there a way to display the data with the filter and the correct grouping?

Any help is appreciated
JohnMac328Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
HAVING is filtering on an aggregate, such as 'Give me everyone that made over a million bucks in their lifetime'.  To filter on single rows use WHERE.

Also, filtering on a column does not mean that you have to display it in SELECT, so I removed it.

Also I threw in aliases lv and md in your code just to make it more readable.

SELECT DISTINCT md.main_dept AS ASTNAME, SUM(lv.votelevel) AS TotalVotes, md.ID
FROM leadervotes lv 
	JOIN Main_Department md ON lv.astdept = md.ID
WHERE lv.votedate > CONVERT(DATETIME, '2013-08-01 00:00:00', 102))
GROUP BY md.main_dept, md.ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnMac328Author Commented:
That was it - thanks!
0
PortletPaulfreelancerCommented:
Just a tiny point on the date filter, it would be more conventional to use >=

whilst it may be unlikely there is a record with a precise time of 00:00:00, it is possible.

WHERE lv.votedate >= CONVERT(DATETIME, '2013-08-01 00:00:00', 102))
                                       ^
0
JohnMac328Author Commented:
Good point - I am actually using this

WHERE     leadervotes.votedate >= <cfqueryparam value="#firstDayOfLastMonth#"cfsqltype="cf_sql_date">
AND leadervotes.votedate < <cfqueryparam value="#firstDayOfThisMonth#"cfsqltype="cf_sql_date">

I left it out to make the question cleaner
0
PortletPaulfreelancerCommented:
>>I left it out to make the question cleaner
not for me :)

cheers, >= was the point, I'm happy now. appreciate the reply.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.