Do I have data for every hour of the day? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Do I have data for every hour of the day?

Author

Topic

CarpenterA
Starting Member

1 Post

Posted - 2009-03-11 : 07:16:34

Hello,

I have a table of events that is populated from hourly flat-text files.

I need a query that will check that I have a complete data-set for any given day (i.e. successfully imported the file for each hour).

I'm currently doing the following and with a rowcount to see that I have 24 rows, can anyone point me in the direction of a more efficient way?

SELECT COUNT(DATEPART(hh, [Date and Time])) AS [hour]
FROM icm_calldetail
WHERE DATEPART(yyyy, [Date and Time]) = '2009' AND DATEPART(mm, [Date and Time]) = '02' AND DATEPART(dd, [Date and Time]) = '18'
GROUP BY DATEPART(hh, [Date and Time])

Many thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-11 : 07:21:48

SELECT COUNT(DATEPART(hh, [Date and Time])) AS [hour]
FROM icm_calldetail
WHERE [Date and Time]>= '20090218' and [Date and Time]< '20090219'
GROUP BY DATEPART(hh, [Date and Time])

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:47:22

SELECT COUNT(DISTINCT DATEPART(hh, [Date and Time])) AS [hour]
FROM icm_calldetail
WHERE [Date and Time]>= '20090218' and [Date and Time]< '20090219'
GROUP BY DATEPART(hh, [Date and Time])

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources