now basically I need to create a hourly status report how many file we received in hour. (based on CreatedDate) issue is I want to display '0' if for any hour we didn't received any request.
so result should be like this. 24 columns as one column for every hour and first column is for date.
Select CreatedDate,[1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24]
from
(
Select ID,FileName,CreatedDate,DATEPART(hour,CreatedDate)Hourly
from Table
)P
PIVOT
( COUNT(FileName) for Hourly in ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24])) as HourlyFilecount
Only thing is now some dates are missing like spouse if I don't have any count for 12/10/2012 then this date will not display but I want it should be on daily basis regardless of counts. If there is no counts then date still should display with all hours are 0.
SQL Query
Posted - 12/11/2012 : 16:23:41
,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24]
from
(
Select ID,FileName,CreatedDate,DATEPART(hour,CreatedDate)Hourly
from Table
)P
PIVOT
( COUNT(FileName) for Hourly in ([1],[2],[3],[4],[5],[6],[7],