<someone@js.com> wrote in message
news:O7Lt6EBlHHA.3280@TK2MSFTNGP05.phx.gbl...
> hi, I have date field and I want to group by it in weekday, and hour,
> For example:
>
> Mon Tue Wed Tur Fri Sat Sun
> 1 am 3 1 2 5 3 0 2
> 2 am 1 2 2 5 3 0 2
> ....
>
> how to do that? Thanks.
>
>
>

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:u7yXpPBlHHA.5048@TK2MSFTNGP04.phx.gbl...
> <someone@js.com> wrote in message
> news:O7Lt6EBlHHA.3280@TK2MSFTNGP05.phx.gbl...
> > hi, I have date field and I want to group by it in weekday, and hour,
> > For example:
> >
> > Mon Tue Wed Tur Fri Sat Sun
> > 1 am 3 1 2 5 3 0 2
> > 2 am 1 2 2 5 3 0 2
> > ....
> >
> > how to do that? Thanks.
> >
> >
> >
>
> Here's one method (untested)
>
> WITH t AS
> (SELECT dt,
> LEFT(DATENAME(DW,dt),3) DayOfWeek,
> DATEPART(HOUR,dt) AS HourOfDay
> FROM tbl
> GROUP BY dt,
> LEFT(DATENAME(DW,dt),3),
> DATEPART(HOUR,dt))
> SELECT HourOfDay, Mon, Tue, Wed, Thu, Fri, Sat, Sun
> FROM t
> PIVOT (
> COUNT(dt)
> FOR DayOfWeek IN (Mon, Tue, Wed, Thu, Fri, Sat, Sun)
> ) AS pvt;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>