Systems Engineering and RDBMS

Getting the counts in hour intervals

Posted by decipherinfosys on May 24, 2010

A friend recently asked a question about how to split the counts for a given day into 4 hour intervals for an event that happens in their system.  The scenario was quite simple – they have meetings that happen every day and there is an activation event that is done before that meeting is made public to their end clients.  He wanted to build out a report showing the total count of the meetings that were activated on a given day and then their break up in 4 hour intervals to track down how the progress is made during the day by the operational staff.  A sub-report detailing the work metrics by team members was also done based on those time intervals to track the efficiency of the workers.

Here is the simple SQL to make it possible – this simply groups the data by the date and since the intervals are known, uses a simple logic to put the data in their respective buckets:

SELECT
CAST(CONVERT(VARCHAR(10), MEETING_ACTIVATION_DATE, 101) AS DATETIME) AS MEETING_ACTIVATION_DATE
,    COUNT(*) AS TOTAL_CNT
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 1 and 4 then 1 else null end) AS HOUR_1_4
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 5 and 8 then 1 else null end) AS HOUR_5_8
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 9 and 12 then 1 else null end) AS HOUR_9_12
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 13 and 16 then 1 else null end) AS HOUR_13_16
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 17 and 20 then 1 else null end) AS HOUR_17_20
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 21 and 24 then 1 else null end) AS HOUR_21_24
FROM dbo.MEETING_MASTER
WHERE MEETING_ACTIVATION_DATE IS NOT NULL
GROUP BY CONVERT(VARCHAR(10), MEETING_ACTIVATION_DATE, 101)
ORDER BY CAST(CONVERT(VARCHAR(10), MEETING_ACTIVATION_DATE, 101) AS DATETIME) DESC
GO

Same thing can be done in Oracle by using the TRUNC() function with date arithmetic or the INTERVAL/EXTRACT functionality.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: