I was working on a query today that I have never tackled before and thought I would share.  We wanted to see what our highest one day total for applications received were on our carrers page.

First step was to group by each of the date.  I am sure there is a DateTime function in SQL that I don't know that would have made this eaiser, but I got arround my limitation with DATEPART.  I extracted the year, the month and the day and grouped by each of them.

GROUP BY DATEPART(yy, CreateDate), DATEPART(mm, CreateDate), DATEPART(DD, CreateDate)

Then it is as simple as counting one of the elements that has been grouped.  My final query:

SELECT COUNT(DATEPART(yy, CreateDate)) AS Total, 
DATEPART(yy, CreateDate) AS Year, 
DATEPART(mm, CreateDate) AS Month,
DATEPART(DD, CreateDate) AS Day
FROM JobApplications
GROUP BY DATEPART(yy, CreateDate), DATEPART(mm, CreateDate), DATEPART(DD, CreateDate)
ORDER BY Year DESC, Month DESC, Day DESC

There it is, a total number of applications recieved per day.