Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day going back 3 months?

I am using datediff but it would be great if I can have a field with the actual date and a count in the resultset.

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())
share|improve this question
    
Does this fall under too basic? Does this belong on SO? –  jcolebrand Mar 2 '11 at 21:00
    
@jcolebrand do you have an answer or not? –  kacalapy Mar 2 '11 at 21:01
1  
yes, and so does Google. I just want to contribute to the site being a little more like what has been discussed on Meta, and a little less "plz gimme teh codez" –  jcolebrand Mar 2 '11 at 21:02
add comment

2 Answers

For starters, why are you reversing your fields in the datediff?

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())

Number two, why does this not work for you?

SELECT crDate, COUNT(*)
FROM tracexml
GROUP BY crDate
share|improve this answer
1  
Also note: I smell a revised set of requirements coming on that makes this query actually answerable the way that he wants it answered. –  jcolebrand Mar 2 '11 at 21:06
add comment

How about the following. I even added a few extra columns if you wanted to put it into a nifty little pivot table where you could group by year, month, day

SELECT 
    CONVERT(VARCHAR(10),crDate,120) as [crDate]
    ,datePart(yy,CONVERT(VARCHAR(10),crDate,120) )  as [crYear]
    ,datePart(mm,CONVERT(VARCHAR(10),crDate,120) )  as [crMonth]
    ,datePart(dd,CONVERT(VARCHAR(10),crDate,120) )  as [crDay]
    , COUNT(*) as [traceCount]
FROM tracexml
WHERE crDate > dateAdd(d,-30,CONVERT(VARCHAR(10),getDate(),120))
GROUP BY CONVERT(VARCHAR(10),crDate,120)
    ,datePart(yy,CONVERT(VARCHAR(10),crDate,120) )  
    ,datePart(mm,CONVERT(VARCHAR(10),crDate,120) )  
    ,datePart(dd,CONVERT(VARCHAR(10),crDate,120) )  
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.