Daily Report Using DATEPART in SQL

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.

28 comments » Write a comment

  1. Is this query can automatic generate the report? How if I want to set to generate report every day 4pm?

    What is the solution?

  2. Hi – very great web site you have created. I enjoyed reading this posting. I did want to write a comment to tell you that the design of this site is very aesthetically pleasing. I used to be a graphic designer, now I am a copy editor in chief. I have always enjoyed playing with information processing systems and am attempting to learn code in my free time.

  3. Great website…and cool article man…thanx for the great post…keep on posting such articles… Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.

Leave a Reply

Required fields are marked *.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>