Solved: Breakdown Data into 1/2 hourly

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

sanjshah12🇬🇧

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

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Member_2_6397083🇧🇪

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.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

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

Signing up is free and takes 30 seconds. No credit card required.

Create Account

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

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

sanjshah12🇬🇧

ASKER

Many thanks Paul, lovely explanation and very useful

PortletPaul🇦🇺

Thank you! Cheers, Paul

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.