Dagoosh!

Randomsauce

Daily Report Using DATEPART in SQL

Posted Tue, Mar 10, 2009

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.

Tagged report datepart t-sql sql

More from Dagoosh!
Shakira

Midway's The GRID: What You Get

Ode to the McGriddle

Pears Before Twitter

Google: Press Enter to Search

© 1999 - 2024 Dagoosh! LLC