Group By Date?

How would I group records by date from a table where each record has a
smalldatetime field called CreationDateTime that is auto-populated using
GetDate().
I need to produce a trends graph and want display the number of records
created per day. Obviously, my CreationDateTime field stored both both date
and time.
Thanks
BenThis isn't going to be very snappy, but...
SELECT
dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime)),
COUNT(*)
FROM
yourTable
WHERE
CreationDateTime >= ?
AND CreationDateTime < ?
GROUP BY
DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime))
ORDER BY
1
Note that if there are days in your date range with no data, they will not
show up in the result set. If you want to have a row for every day, even
when there are no relevant rows, use a calendar table (see
http://www.aspfaq.com/2519 for some examples).
You may want to consider adding a computed or static column that holds the
date only, if you are going to use a lot of queries like this. If you do
that, you will want to experiment with your clustered index, and whether it
resides on the column with both date and time, or on the column with just
the date. Your best scenario depends on whether you are querying by range
or just analyzing the entire table, and what else this table is being used
for...
A
"Ben Fidge" <ben.fidge@.nospambtopenworld.com> wrote in message
news:%23Yd2cbMBGHA.740@.TK2MSFTNGP12.phx.gbl...
> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both
> date and time.
> Thanks
> Ben
>|||Ben Fidge wrote:

> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both dat
e
> and time.
> Thanks
> Ben
SELECT MIN(creationdatetime) AS dt, COUNT(*) AS cnt
FROM your_table
GROUP BY DATEDIFF(DAY,'20000101',creationdatetime
) ;
David Portas
SQL Server MVP
--|||Exceelnt, works a treat. Thanks.
Ben
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u0D$9fMBGHA.1032@.TK2MSFTNGP11.phx.gbl...
> This isn't going to be very snappy, but...
>
> SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime)),
> COUNT(*)
> FROM
> yourTable
> WHERE
> CreationDateTime >= ?
> AND CreationDateTime < ?
> GROUP BY
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime))
> ORDER BY
> 1
>
> Note that if there are days in your date range with no data, they will not
> show up in the result set. If you want to have a row for every day, even
> when there are no relevant rows, use a calendar table (see
> http://www.aspfaq.com/2519 for some examples).
> You may want to consider adding a computed or static column that holds the
> date only, if you are going to use a lot of queries like this. If you do
> that, you will want to experiment with your clustered index, and whether
> it resides on the column with both date and time, or on the column with
> just the date. Your best scenario depends on whether you are querying by
> range or just analyzing the entire table, and what else this table is
> being used for...
> A
>
> "Ben Fidge" <ben.fidge@.nospambtopenworld.com> wrote in message
> news:%23Yd2cbMBGHA.740@.TK2MSFTNGP12.phx.gbl...
>