sql server programming: Hello,

I'm writing a bunch of reports that are grouped by day.
I've been selecting convert(char(10), sh.timeread, 112)
and grouping by that. I also tried MONTH(sh.timeread), DAY
(sh.timeread) and grouping accordingly. My question is
what is the most efficent way in T-SQL to group by day?

Regards,

Bryan,

Often the most efficient solution is to use a separate calendar table
to help with the grouping. It will look something like this:

create table Calendar (
d smalldatetime not null primary key,
.. other columns for weekend/weekday, holiday, etc.
)
insert into Calendar ... [all the days you'll need]

select
Calendar.d,
sum(sh.this),
min(sh.that),
max(sh.other)
from Calendar join YourTable sh
on sh.timeread >= d and sh.timeread < d + 1
group by Calendar.d

With the right indexing on your table, this will be very efficient.
You'll want one index to include all the columns referred to in the
table. Basically, put columns appearing alone (not within an
expression) in the GROUP BY clause first, followed by timeread and other
columns that appear only in aggregates. If it's a clustered index, you
only need to specify additional columns that appear in comparisons in
the query. If it's a nonclustered index, you need to specify all column
referred to, so nothing has to be looked up outside the index.

If you want to include dates in your output for which there are no
corresponding rows in your table, you can use an outer join. And other
variations are possible, when needed, such as weighting aggregates by
day of the week: sum(case when Calendar.Weekend = 1 then 2 else 1 end *
sh.this), etc.

You'll find good information here: http://www.aspfaq.com/show.asp?id=2519

Steve Kass
Drew University

[quoted text, click to view]