SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query

Author

Topic

IK1972

56 Posts

Posted - 2012-12-11 : 16:23:41

I have one table with 3 column

ID,FileName, CreatedDate
1, abc.txt, getdate()

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.

---------------------- Day Hours -----------------
Date, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, .... 20, 21, 22, 23

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 21:27:29

[code]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[/code]

IK1972

56 Posts

Posted - 2012-12-12 : 08:19:52

Thanks sodeep,

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.

Any help really appreciated.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-12 : 12:41:22

Why don't you put all the dates in that table and based on that you can use it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources