asked on

Breakdown Data into 1/2 hourly

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

Christophe Gillent

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.

ste5an

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

sanjshah12

ASKER

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.

sanjshah12

ASKER

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,

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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 Todd

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 ...

sanjshah12

ASKER

Many thanks Paul, lovely explanation and very useful

PortletPaul

Thank you! Cheers, Paul