Breakdown Data into 1/2 hourly

sanjshah12
sanjshah12 used Ask the Experts™
on
Hi,

I have a stored procedure that aggregates data hourly, but would like to also split the data half hourly, I'm not sure the best approach would be and welcome any pointers:

	@DateTimeToFilter DATETIME

BEGIN

	SET NOCOUNT ON;
	
	WITH H ( [Hour] ) AS
	(	SELECT  7 		UNION 
		SELECT  8 		UNION 
		SELECT  9 		UNION 
		SELECT  10		UNION 
		SELECT  11		UNION 
		SELECT  12		UNION 
		SELECT  13		UNION 
		SELECT  14		UNION 
		SELECT  15		UNION 
		SELECT  16		UNION 
		SELECT  17		UNION 
		SELECT  18		UNION 
		SELECT  19
	)
	SELECT	
		H.[Hour], COUNT(T.BookingID) AS NoOfUsers
	FROM	
		H LEFT JOIN tbl_Visitors T ON H.[Hour] = DATEPART(HOUR, T.BookedInTime) AND
			((DATEDIFF(dd, T.BookedInTime, @DateTimeToFilter) = 0) AND 
			(DATEDIFF(mm, T.BookedInTime, @DateTimeToFilter) = 0) AND 
			(DATEDIFF(yy, T.BookedInTime, @DateTimeToFilter) = 0))
	GROUP BY 
		H.[Hour];

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Christophe GillentSupport Engineer

Commented:
The easiest way would be to log (and then query) 150 instead of 15 (for 3:00PM) and 155 (for 3:30PM)
No big changes, instant results.
ste5anSenior Developer

Commented:
E.g.
SELECT  DATEDIFF(HOUR, '2000-01-01', create_date) % 24 ,
        COUNT(*)
FROM    sys.tables T
GROUP BY DATEDIFF(HOUR, '2000-01-01', create_date) % 24;

SELECT  DATEDIFF(MINUTE, '2000-01-01', create_date) % 30 ,
        COUNT(*)
FROM    sys.tables T
GROUP BY DATEDIFF(MINUTE, '2000-01-01', create_date) % 30;

Open in new window

Author

Commented:
Thanks steSan for your message, looking at your query I can see that this is separated into 2 however as you can see from the above I return the query from 7 to 7 (7am to 7pm), I'm not sure how I can use your query to do this, any help is appreciated.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks ChrisGillent for your reply, I'm not quite sure what you mean log 150, presently the data is stored as:

2014-06-05 14:00:00
2014-06-05 16:45:00
2014-06-05 16:45:00

Regards,
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
I have prepared an approach for you that uses a recursive CTE. It changes the parameter type to DATE which is done to ensure the time of day of the parameter does not interfere with subsequent calculations. This approach can be changed if needed. It then builds a small CTE stating at 07:00 of the day running up by 30 minutes until the time of day finished at 19:00

The visit data is then left joined to this set of datetime boundaries and aggregated like you did previously.

I am NOT sure that your existing calculation of visitors is correct. you are calculating number of visits, not the number of users. To demonstrate this I have 2 count columns so you can see the difference. Please take note  of the data being used when comparing the columns. I only have 3 users (1,2,3) in that data. Scroll down to see results.
DECLARE @DateTimeToFilter date

SET @DateTimeToFilter = '20140619'

;
WITH
      CTE
      AS (
                  SELECT
                        /* 420 minutes = 7 hours */
                        DATEADD(MINUTE, 420, CAST(@DateTimeToFilter AS datetime)) AS low
                      , DATEADD(MINUTE, 450, CAST(@DateTimeToFilter AS datetime)) AS high
                  UNION ALL
                        SELECT
                              DATEADD(MINUTE, 30, low)  AS low
                            , DATEADD(MINUTE, 30, high) AS high
                        FROM CTE
                        WHERE high < DATEADD(HOUR, 19, CAST(@DateTimeToFilter AS datetime))
            )
SELECT
      CTE.low
    , CTE.high
    , COUNT(T.BookingID)          AS NoOfUsers
    , COUNT(DISTINCT T.BookingID) AS NoOfDistinctUsers
FROM CTE
      LEFT JOIN tbl_Visitors AS t
            ON t.BookedInTime >= CTE.low
                  AND t.BookedInTime < CTE.high
GROUP BY
      CTE.low
    , CTE.high
;
     

**[Results]**:
    
    |                         LOW |                        HIGH | NOOFUSERS | NOOFDISTINCTUSERS |
    |-----------------------------|-----------------------------|-----------|-------------------|
    | June, 19 2014 07:00:00+0000 | June, 19 2014 07:30:00+0000 |         7 |                 3 |
    | June, 19 2014 07:30:00+0000 | June, 19 2014 08:00:00+0000 |         7 |                 3 |
    | June, 19 2014 08:00:00+0000 | June, 19 2014 08:30:00+0000 |         7 |                 3 |
    | June, 19 2014 08:30:00+0000 | June, 19 2014 09:00:00+0000 |         7 |                 3 |
    | June, 19 2014 09:00:00+0000 | June, 19 2014 09:30:00+0000 |         7 |                 3 |
    | June, 19 2014 09:30:00+0000 | June, 19 2014 10:00:00+0000 |         7 |                 3 |
    | June, 19 2014 10:00:00+0000 | June, 19 2014 10:30:00+0000 |         7 |                 3 |
    | June, 19 2014 10:30:00+0000 | June, 19 2014 11:00:00+0000 |         7 |                 3 |
    | June, 19 2014 11:00:00+0000 | June, 19 2014 11:30:00+0000 |         7 |                 3 |
    | June, 19 2014 11:30:00+0000 | June, 19 2014 12:00:00+0000 |         7 |                 3 |
    | June, 19 2014 12:00:00+0000 | June, 19 2014 12:30:00+0000 |         7 |                 3 |
    | June, 19 2014 12:30:00+0000 | June, 19 2014 13:00:00+0000 |         7 |                 3 |
    | June, 19 2014 13:00:00+0000 | June, 19 2014 13:30:00+0000 |         7 |                 3 |
    | June, 19 2014 13:30:00+0000 | June, 19 2014 14:00:00+0000 |         7 |                 3 |
    | June, 19 2014 14:00:00+0000 | June, 19 2014 14:30:00+0000 |         2 |                 2 |
    | June, 19 2014 14:30:00+0000 | June, 19 2014 15:00:00+0000 |         0 |                 0 |
    | June, 19 2014 15:00:00+0000 | June, 19 2014 15:30:00+0000 |         0 |                 0 |
    | June, 19 2014 15:30:00+0000 | June, 19 2014 16:00:00+0000 |         0 |                 0 |
    | June, 19 2014 16:00:00+0000 | June, 19 2014 16:30:00+0000 |         0 |                 0 |
    | June, 19 2014 16:30:00+0000 | June, 19 2014 17:00:00+0000 |         0 |                 0 |
    | June, 19 2014 17:00:00+0000 | June, 19 2014 17:30:00+0000 |         0 |                 0 |
    | June, 19 2014 17:30:00+0000 | June, 19 2014 18:00:00+0000 |         0 |                 0 |
    | June, 19 2014 18:00:00+0000 | June, 19 2014 18:30:00+0000 |         0 |                 0 |
    | June, 19 2014 18:30:00+0000 | June, 19 2014 19:00:00+0000 |         0 |                 0 |

Open in new window

Compare this to your existing query on the same data, scroll to see results:
**Query 2**:

    declare @DateTimeToFilter DATETIME
    
    set @DateTimeToFilter = '20140619'
    
    ;WITH H ( [Hour] ) AS
    	(	SELECT  7 		UNION 
    		SELECT  8 		UNION 
    		SELECT  9 		UNION 
    		SELECT  10		UNION 
    		SELECT  11		UNION 
    		SELECT  12		UNION 
    		SELECT  13		UNION 
    		SELECT  14		UNION 
    		SELECT  15		UNION 
    		SELECT  16		UNION 
    		SELECT  17		UNION 
    		SELECT  18		UNION 
    		SELECT  19
    	)
    	SELECT	
    		H.[Hour], COUNT(T.BookingID) AS NoOfUsers
    	FROM	
    		H LEFT JOIN tbl_Visitors T ON H.[Hour] = DATEPART(HOUR, T.BookedInTime) AND
    			((DATEDIFF(dd, T.BookedInTime, @DateTimeToFilter) = 0) AND 
    			(DATEDIFF(mm, T.BookedInTime, @DateTimeToFilter) = 0) AND 
    			(DATEDIFF(yy, T.BookedInTime, @DateTimeToFilter) = 0))
    	GROUP BY 
    		H.[Hour]
    

**[Results][3]**:
    
    | HOUR | NOOFUSERS |
    |------|-----------|
    |    7 |        14 |
    |    8 |        14 |
    |    9 |        14 |
    |   10 |        14 |
    |   11 |        14 |
    |   12 |        14 |
    |   13 |        14 |
    |   14 |         2 |
    |   15 |         0 |
    |   16 |         0 |
    |   17 |         0 |
    |   18 |         0 |
    |   19 |         0 |


**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE tbl_Visitors 
    	([BookedInTime] datetime, [BookingID] int)
    ;
    	
    INSERT INTO tbl_Visitors 
    	([BookedInTime], [BookingID])
    VALUES
    	('2014-06-19 07:00:01', 1),
    	('2014-06-19 07:04:20', 2),
    	('2014-06-19 07:08:39', 3),
    	('2014-06-19 07:12:59', 1),
    	('2014-06-19 07:17:18', 2),
    	('2014-06-19 07:21:37', 3),
    	('2014-06-19 07:25:56', 1),
    	('2014-06-19 07:30:15', 2),
    	('2014-06-19 07:34:35', 3),
    	('2014-06-19 07:38:54', 1),
    	('2014-06-19 07:43:13', 2),
    	('2014-06-19 07:47:32', 3),
    	('2014-06-19 07:51:51', 1),
    	('2014-06-19 07:56:11', 2),
    	('2014-06-19 08:00:30', 3),
    	('2014-06-19 08:04:49', 1),
    	('2014-06-19 08:09:08', 2),
    	('2014-06-19 08:13:27', 3),
    	('2014-06-19 08:17:47', 1),
    	('2014-06-19 08:22:06', 2),
    	('2014-06-19 08:26:25', 3),
    	('2014-06-19 08:30:44', 1),
    	('2014-06-19 08:35:03', 2),
    	('2014-06-19 08:39:23', 3),
    	('2014-06-19 08:43:42', 1),
    	('2014-06-19 08:48:01', 2),
    	('2014-06-19 08:52:20', 3),
    	('2014-06-19 08:56:39', 1),
    	('2014-06-19 09:00:59', 2),
    	('2014-06-19 09:05:18', 3),
    	('2014-06-19 09:09:37', 1),
    	('2014-06-19 09:13:56', 2),
    	('2014-06-19 09:18:15', 3),
    	('2014-06-19 09:22:35', 1),
    	('2014-06-19 09:26:54', 2),
    	('2014-06-19 09:31:13', 3),
    	('2014-06-19 09:35:32', 1),
    	('2014-06-19 09:39:51', 2),
    	('2014-06-19 09:44:11', 3),
    	('2014-06-19 09:48:30', 1),
    	('2014-06-19 09:52:49', 2),
    	('2014-06-19 09:57:08', 3),
    	('2014-06-19 10:01:27', 1),
    	('2014-06-19 10:05:47', 2),
    	('2014-06-19 10:10:06', 3),
    	('2014-06-19 10:14:25', 1),
    	('2014-06-19 10:18:44', 2),
    	('2014-06-19 10:23:03', 3),
    	('2014-06-19 10:27:23', 1),
    	('2014-06-19 10:31:42', 2),
    	('2014-06-19 10:36:01', 3),
    	('2014-06-19 10:40:20', 1),
    	('2014-06-19 10:44:39', 2),
    	('2014-06-19 10:48:59', 3),
    	('2014-06-19 10:53:18', 1),
    	('2014-06-19 10:57:37', 2),
    	('2014-06-19 11:01:56', 3),
    	('2014-06-19 11:06:15', 1),
    	('2014-06-19 11:10:35', 2),
    	('2014-06-19 11:14:54', 3),
    	('2014-06-19 11:19:13', 1),
    	('2014-06-19 11:23:32', 2),
    	('2014-06-19 11:27:51', 3),
    	('2014-06-19 11:32:11', 1),
    	('2014-06-19 11:36:30', 2),
    	('2014-06-19 11:40:49', 3),
    	('2014-06-19 11:45:08', 1),
    	('2014-06-19 11:49:27', 2),
    	('2014-06-19 11:53:47', 3),
    	('2014-06-19 11:58:06', 1),
    	('2014-06-19 12:02:25', 2),
    	('2014-06-19 12:06:44', 3),
    	('2014-06-19 12:11:03', 1),
    	('2014-06-19 12:15:23', 2),
    	('2014-06-19 12:19:42', 3),
    	('2014-06-19 12:24:01', 1),
    	('2014-06-19 12:28:20', 2),
    	('2014-06-19 12:32:39', 3),
    	('2014-06-19 12:36:59', 1),
    	('2014-06-19 12:41:18', 2),
    	('2014-06-19 12:45:37', 3),
    	('2014-06-19 12:49:56', 1),
    	('2014-06-19 12:54:15', 2),
    	('2014-06-19 12:58:35', 3),
    	('2014-06-19 13:02:54', 1),
    	('2014-06-19 13:07:13', 2),
    	('2014-06-19 13:11:32', 3),
    	('2014-06-19 13:15:51', 1),
    	('2014-06-19 13:20:11', 2),
    	('2014-06-19 13:24:30', 3),
    	('2014-06-19 13:28:49', 1),
    	('2014-06-19 13:33:08', 2),
    	('2014-06-19 13:37:27', 3),
    	('2014-06-19 13:41:47', 1),
    	('2014-06-19 13:46:06', 2),
    	('2014-06-19 13:50:25', 3),
    	('2014-06-19 13:54:44', 1),
    	('2014-06-19 13:59:03', 2),
    	('2014-06-19 14:03:23', 3),
    	('2014-06-19 14:07:42', 1)
    ;

Open in new window

http://sqlfiddle.com/#!3/a6665/3

{+ edit}
I may have misunderstood what the COUNT() column is doing, and as you didn't supply sample data I may have just invented an issue that does not exist - sorry.

Moral of this tale is: always provide sample data otherwise we are guessing

{+ edit 2}
before I forget. Please do NOT be tempted to use "between" in the join of the CTE to your data. The way I have it setup is accurate.

For more on this topic please see: "Beware of Between"
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
TIP: Do you know that UNION when used by itself is "more expensive" than UNION ALL?
UNION does a "distinct sort" so the in the final result no 2 rows are the same. UNION ALL does not do this step.

below you should use UNION ALL because 7 is different to 8 is different to 9 ... you do not need the extra work the UNION does
	(	SELECT  7 		UNION 
		SELECT  8 		UNION 
		SELECT  9 		UNION 
		SELECT  10		UNION 
		SELECT  11		UNION 
		SELECT  12		UNION 
		SELECT  13		UNION 
		SELECT  14		UNION 
		SELECT  15		UNION 
		SELECT  16		UNION 
		SELECT  17		UNION 
		SELECT  18		UNION 
		SELECT  19

Open in new window

David ToddSenior Database Administrator

Commented:
Hi,

--If 
select dateadd( hour, datediff( hour, 0, getdate()), 0 )
--rounds to the hour, and
select dateadd( minute, datediff( minute, 0, getdate()), 0 )
--rounds to the minute, try this:
select dateadd( minute, ( datediff( minute, 0, getdate()) / 30 ) * 30, 0 )
--The divide by 30 should truncate as its integer maths.

Open in new window


HTH
  David

PS Assumes that your times are in a datetime or similar, replace getdate() with the tablealias.columname ...

Author

Commented:
Many thanks Paul, lovely explanation and very useful
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Thank you! Cheers, Paul

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial