asked on T-SQL: How do you group by daily date? What is the design pattern/best practice?
I occasionally need to generate a report using SQL that rolls up values by date of month... say the last 30 days or last 60 days, etc., of some data.
What is the query design pattern for this?
The code below works but seems too complicated. It groups by the number of calendary days since 2007, then later "re-constitutes" the date by adding water (er, calling DateAdd).
A colleague also mentioned grouping by Floor(Cast(MyDateField as float)) but that still seems indirect.
Does Microsoft have a nice solution for this?
Does everyone have to do this funny stuff just to generate a report by date?
What is the query design pattern for this?
The code below works but seems too complicated. It groups by the number of calendary days since 2007, then later "re-constitutes" the date by adding water (er, calling DateAdd).
A colleague also mentioned grouping by Floor(Cast(MyDateField as float)) but that still seems indirect.
Does Microsoft have a nice solution for this?
Does everyone have to do this funny stuff just to generate a report by date?
select DateAdd(day,Days2007,'1-1-2007'),CNT from (
select datediff(day, '1-1-2007', MyDateField) as Days2007, count(PKField) as CNT
from MyTable
group by datediff(day, '1-1-2007', MyDateField)
) subQueryAlias
order by Days2007 desc
Microsoft SQL Server
Last Comment
ZuZuPetals
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
to restrict the data, you can use WHERE clasue
select MyDateField as Days2007, count(PKField) as CNT
from (select dateadd(dd,0,datediff(dd,0,MyDateField)) as MyDateField,PKField
from YourTable where MyDateField >= '2009-02-01' and MyDateField < '2009-03-01') as t
group by MyDateField
order by MyDateField desc
or you can avoid the derived table
select dateadd(dd,0,datediff(dd,0,MyDateField)) as MyDateField,count(PKField) CNT
from YourTable
where MyDateField >= '2009-02-01' and MyDateField < '2009-03-01'
group by dateadd(dd,0,datediff(dd,0,MyDateField))
order by dateadd(dd,0,datediff(dd,0,MyDateField)) desc
ASKER
The solution I accepted actually doesn't seem to work:
SELECT FLOOR(GETDATE())
=> Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
SELECT FLOOR(GETDATE())
=> Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
Open in new window