SQL Server 2008: Row count for each hour in a Day and Row count for each day in a Month I am trying to get row count in a given week for each day as below, which works fine

SELECT 
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Sunday' THEN 1 END) AS S,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Monday' THEN 1 END) AS M,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Tuesday' THEN 1 END) AS T,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Wednesday' THEN 1 END) AS W,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Thursday' THEN 1 END) AS R,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Friday' THEN 1 END) AS F,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Saturday' THEN 1 END) AS S,
    COUNT(*) AS TOTAL
FROM PARCEL
WHERE [created_at] BETWEEN '2017-03-26 00:00:00'/*Sunday, report start date*/ AND '2017-04-10 23:59:59'/* Saturday, report end date */


I am also trying to get row count for each hour in a given day as below

Hour1 Hour2 Hour3 Hour4 .............Hour24  Total
 33    544   55     77                 23    13444


Also row count for each day in a given month as below

 Day1  Day2  Day3   Day4 .............Day31  Total
 33    544   55     77                 23    13444


I am pretty new to sql programming, am not sure where to start on this for tha lateral 2 reports, can someone please help me with this?
You want to look into using a PIVOT table.  You can use the same solution for your hour as well as the day of week or even day in month just change a few things in the query.  

Here is an example for you that gives you the Sunday, Monday, etc. table that you first asked for.  It should get you started. 

;WITH CTE_Parcel AS
    (
    SELECT created_at
        , 1 AS ItemCount
        , DATENAME(WEEKDAY, created_at) AS DayOfOrder
        , DATEPART(HOUR, created_at) AS HourOfOrder
        , SundayOfWeek = DATEADD(DAY , 7-DATEPART(WEEKDAY,created_at),created_at)
    FROM PARCEL
    WHERE created_at BETWEEN '2017-03-26 00:00:00'/*Sunday, report start date*/ AND '2017-04-10 23:59:59'/* Saturday, report end date */
    )
SELECT SundayOfWeek
    , PT.Sunday
    , PT.Monday
    , PT.Tuesday
    , PT.Wednesday
    , PT.Thursday
    , PT.Friday
    , PT.Saturday 
    , WeekTotal = PT.Sunday + PT.Monday + PT.Tuesday + PT.Wednesday + PT.Thursday + PT.Friday + PT.Saturday 
FROM (SELECT ItemCount, DayOfOrder, HourOfOrder, SundayOfWeek FROM CTE_Parcel) AS P
    PIVOT (COUNT(ItemCount) FOR DayOfOrder IN ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])) AS PT
ORDER BY PT.SundayOfWeek