Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Count by 30 min interval query
Add to briefcase

Count by 30 min interval query Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
bquintana
Posted Monday, February 18, 2013 3:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:37 PM
Points: 4, Visits: 12
I need help in querying a table for volume by 30 minute interval by day month by month.

Table dbo.HisT contained info in this format

CallDateTime HistID
2013-02-01 06:14:41.000 2671058
2013-02-01 07:02:15.000 2671057
2013-02-01 07:06:41.000 2671059
2013-02-01 07:07:20.000 2671069
2013-02-01 07:22:57.000 2671068
2013-02-01 07:38:15.000 2671067
2013-02-01 07:42:48.000 2671066
2013-02-01 07:53:47.000 2671065
2013-02-01 08:02:37.000 2671064
2013-02-01 08:07:04.000 2671063
2013-02-01 08:11:09.000 2671062
2013-02-01 08:22:12.000 2671061
2013-02-01 08:22:48.000 2671060

Results should show something like this

06:00-06:30 1
06:30-07:00 0
07:00-07:30 4
07:30-08:00 3
08:00-08:30 5

Thank you for any insight
Post #1421357
Jeff Moden
Posted Monday, February 18, 2013 4:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792
bquintana (2/18/2013)
I need help in querying a table for volume by 30 minute interval by day month by month.

Table dbo.HisT contained info in this format

CallDateTime HistID
2013-02-01 06:14:41.000 2671058
2013-02-01 07:02:15.000 2671057
2013-02-01 07:06:41.000 2671059
2013-02-01 07:07:20.000 2671069
2013-02-01 07:22:57.000 2671068
2013-02-01 07:38:15.000 2671067
2013-02-01 07:42:48.000 2671066
2013-02-01 07:53:47.000 2671065
2013-02-01 08:02:37.000 2671064
2013-02-01 08:07:04.000 2671063
2013-02-01 08:11:09.000 2671062
2013-02-01 08:22:12.000 2671061
2013-02-01 08:22:48.000 2671060

Results should show something like this

06:00-06:30 1
06:30-07:00 0
07:00-07:30 4
07:30-08:00 3
08:00-08:30 5

Thank you for any insight


What do you want to show when the date changes? And, how much of the day do you want to show? All 24 hours or ???

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1421371
bquintana
Posted Tuesday, February 19, 2013 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:37 PM
Points: 4, Visits: 12
Day runs 0800-2000 and I think it would run better if the results came out like this:

Date 08:00-08-30|08:30-0900 | 09:00-09:30 | 09:30-10:00 etc....
02-01-2012 5 2 3 7
02-02-2012 2 3 4 8
etc.....
Post #1421648
Kingston Dhasian
Posted Tuesday, February 19, 2013 7:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 2,221, Visits: 4,184
You can use Dynamic CROSS-TABS to achieve this

You can have a look at the below mentioned articles for more details on CROSS-TABS
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/

Something like below

DECLARE	@strColumns VARCHAR(MAX)
DECLARE	@strSQL VARCHAR(MAX)

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL
	DROP TABLE #tmp_table
	
CREATE TABLE #tmp_table
(
	CallDateTime	DATETIME,
	HistID		INT
)

INSERT	 #tmp_table( CallDateTime, HistID )
SELECT	'2013-02-01 16:14:41.000', 2671058 UNION ALL
SELECT	'2013-02-01 17:02:15.000', 2671057 UNION ALL
SELECT	'2013-02-01 17:06:41.000', 2671059 UNION ALL
SELECT	'2013-02-01 17:07:20.000', 2671069 UNION ALL
SELECT	'2013-02-01 17:22:57.000', 2671068 UNION ALL
SELECT	'2013-02-01 17:38:15.000', 2671067 UNION ALL
SELECT	'2013-02-01 17:42:48.000', 2671066 UNION ALL
SELECT	'2013-02-01 17:53:47.000', 2671065 UNION ALL
SELECT	'2013-02-01 18:02:37.000', 2671064 UNION ALL
SELECT	'2013-02-01 08:07:04.000', 2671063 UNION ALL
SELECT	'2013-02-01 08:11:09.000', 2671062 UNION ALL
SELECT	'2013-02-01 08:22:12.000', 2671061 UNION ALL
SELECT	'2013-02-01 08:22:48.000', 2671060 UNION ALL
SELECT	'2013-02-02 08:22:48.000', 2671060

SELECT	@strColumns = COALESCE( @strColumns + ', ', '' )
		+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) < 30 THEN 1 ELSE 0 END ) AS ['
		+ CAST( 8 + Numbers.number AS VARCHAR(3) ) + '_' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + '30], ' + CHAR(13)
		+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) >= 30 THEN 1 ELSE 0 END ) AS ['
		+ CAST( 830 + Numbers.number * 100 AS VARCHAR(4) ) + '_' + CAST( 9 + Numbers.number AS VARCHAR(3) ) + ']' + CHAR(13)
FROM	(
		SELECT	number
		FROM	master..spt_values
		WHERE	type = 'P' AND number < 12
	) Numbers --===You can use a Tally table instead of this if you have one
		
SET	@strSQL = ' SELECT	CONVERT(VARCHAR(20), CallDateTime, 110) AS Date, '
		+		@strColumns
		+ ' FROM	#tmp_table '
		+ ' GROUP BY    CONVERT(VARCHAR(20), CallDateTime, 110) '
			
EXECUTE( @strSQL )

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL
	DROP TABLE #tmp_table

Edit: Added code as an example

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1421653
Jason-299789
Posted Tuesday, February 19, 2013 7:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124
This is my attempt, it works on a 24 hour widow and im sure theres a better way of doing it

CREATE TABLE #Hist
(
	CallDateTime DateTime
	,HistId int
)

Insert into #Hist
VALUES ('2013-02-01 06:14:41.000',2671058)
		,('2013-02-01 07:02:15.000', 2671057)
		,('2013-02-01 07:06:41.000', 2671059)
		,('2013-02-01 07:07:20.000', 2671069)
		,('2013-02-01 07:22:57.000', 2671068)
		,('2013-02-01 07:38:15.000', 2671067)
		,('2013-02-01 07:42:48.000', 2671066)
		,('2013-02-01 07:53:47.000', 2671065)
		,('2013-02-01 08:02:37.000', 2671064)
		,('2013-02-01 08:07:04.000', 2671063)
		,('2013-02-01 08:11:09.000', 2671062)
		,('2013-02-01 08:22:12.000', 2671061)
		,('2013-02-01 08:22:48.000', 2671060)

Declare @StartDate Datetime = '01-Feb-2013 00:00:00.000'
Declare @endDate Datetime = '02-Feb-2013 00:00:00.000'


;with CTE
AS
(
	Select 
		Row_NUMBER() OVER (ORDER BY object_id) Rn
	from sys.all_columns
)
,Cte_Interval AS
(
	Select  
		DateAdd(minute,(rn-1)*30,@StartDate) StartTime
		, DateAdd(minute,(rn)*30,@StartDate) EndTime
	from CTE
	Where DateAdd(minute,(rn)*30,@StartDate)<=@EndDate
)
Select StartTime,EndTime,count(histId)
from Cte_Interval inter
	LEFT JOIN #Hist hist on hist.CallDateTime >=inter.StartTime
				and hist.CallDateTime<inter.EndTime
group by StartTime,EndTime
order by StartTime

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1421654
Jason-299789
Posted Tuesday, February 19, 2013 9:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124
I saw the Dynamic pivot of the request and wrote this to pivot the data, its similar to dunstans, there are some simplifications optimisations that can be made like splitting the Date and Time parts into separate tables and that would remove the SELECT DISTINCT from the dynamic case build, I also think you can get rid of the LEFT(string, Lenf(String)-1) to remove the last comma, but its been a long day.


/*
	Build working set
*/
CREATE TABLE #Hist
(
	CallDateTime DateTime
	,HistId int
)

Insert into #Hist
VALUES ('2013-02-01 06:14:41.000',2671058)
		,('2013-02-01 07:02:15.000', 2671057)
		,('2013-02-01 07:06:41.000', 2671059)
		,('2013-02-01 07:07:20.000', 2671069)
		,('2013-02-01 07:22:57.000', 2671068)
		,('2013-02-01 07:38:15.000', 2671067)
		,('2013-02-01 07:42:48.000', 2671066)
		,('2013-02-01 07:53:47.000', 2671065)
		,('2013-02-01 08:02:37.000', 2671064)
		,('2013-02-01 08:07:04.000', 2671063)
		,('2013-02-01 08:11:09.000', 2671062)
		,('2013-02-01 08:22:12.000', 2671061)
		,('2013-02-01 08:22:48.000', 2671060)

Insert into #Hist
VALUES ('2013-02-02 06:14:41.000',2671058)
		,('2013-02-02 09:02:15.000', 2671057)
		,('2013-02-02 09:06:41.000', 2671059)
		,('2013-02-02 09:07:20.000', 2671069)
		,('2013-02-02 09:22:57.000', 2671068)
		,('2013-02-02 09:38:15.000', 2671067)
		,('2013-02-02 09:42:48.000', 2671066)
		,('2013-02-02 07:53:47.000', 2671065)
		,('2013-02-02 10:02:37.000', 2671064)
		,('2013-02-02 11:07:04.000', 2671063)
		,('2013-02-02 12:11:09.000', 2671062)
		,('2013-02-02 12:22:12.000', 2671061)
		,('2013-02-02 15:22:48.000', 2671060)

Declare @StartDate Datetime = '01-Feb-2013 00:00:00.000'
Declare @endDate Datetime = '03-Feb-2013 00:00:00.000'

/*
	Create an Inline Tally Table
*/
;with CTE
AS
(
	Select 
		Row_NUMBER() OVER (ORDER BY object_id) Rn
	from sys.all_columns
)
/*
	Generate my intervals (can be changed!!)
*/
,Cte_Interval AS
(
	Select  
		DateAdd(minute,(rn-1)*30,@StartDate) StartTime
		, DateAdd(minute,(rn)*30,@StartDate) EndTime
		,convert(varchar(5),Convert(Time(0),DateAdd(minute,(rn-1)*30,@StartDate)))+'-'
                                          +convert(varchar(5),Convert(Time(0),DateAdd(minute,(rn)*30,@StartDate))) timepart
		,convert(varchar(10),convert(date,DateAdd(minute,(rn-1)*30,@StartDate))) datep
		,rn
	from CTE
	Where DateAdd(minute,(rn)*30,@StartDate)<=@EndDate
)
/*
	Build the data set to group into time slice
*/
Select datep
		,timepart
		,rn % 48 Modulous
		,count(histId) TotalCalls
into #prepivot
from Cte_Interval inter
	LEFT JOIN #Hist hist on hist.CallDateTime >=inter.StartTime
								and hist.CallDateTime<inter.EndTime
group by timepart,datep,rn
order by datep,timepart

/*
	Get ready to build the dynamic pivot
*/
Declare @dm_ct varchar(max)
Declare @pvt varchar(4000) 

/*
	build dynamic CASE for use in pivot using a for XML
*/
Select  @pvt=(Select 'SUM(CASE WHEN modulous=' +convert(varchar(2),modulous) +' Then TotalCalls' +' Else 0 END) AS ['+timepart+'], ' 
		from 
		(Select distinct timepart,Modulous 
			from #prepivot) x 
	FOR XML PATH(''))
/*
	build final Select
*/
Set @dm_ct='Select Datep,'+ Left(@pvt,len(@pvt)-1)+ 'FROM #prepivot GROUP BY DAtep'
	
/*
	Execute dynamic SQL
*/
EXEC (@dm_ct)

/*
	Clean up
*/
Drop Table #Hist
Drop Table #PrePivot

Edited : added comments.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1421711
Jason-299789
Posted Tuesday, February 19, 2013 9:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124
I've made two refinements the first is the addition of a dynamic interval slicer, and the second was to remove the modulous calculation and replace it with a ROW_NUMBER() based on the number of intervals per day. 


Declare @StartDate Datetime = '01-Feb-2013 00:00:00.000'
Declare @endDate Datetime = '03-Feb-2013 00:00:00.000'
Declare @interval tinyint =60 --Interval in minutes.

/*
	Create an Inline Tally Table
*/
;with CTE
AS
(
	Select 
		Row_NUMBER() OVER (ORDER BY object_id) Rn
	from sys.all_columns
)
/*
	Generate my intervals (can be changed!!)
*/
,Cte_Interval AS
(
	Select  
		DateAdd(minute,(rn-1)*@interval,@StartDate) StartTime
		, DateAdd(minute,(rn)*@interval,@StartDate) EndTime
		,convert(varchar(5),Convert(Time(0),DateAdd(minute,(rn-1)*@interval,@StartDate)))+'-'+convert(varchar(5),Convert(Time(0),DateAdd(minute,(rn)*@interval,@StartDate))) timepart
		,convert(varchar(10),convert(date,DateAdd(minute,(rn-1)*@interval,@StartDate))) datep
	from CTE
	Where DateAdd(minute,(rn)*@interval,@StartDate)<=@EndDate
)

/*
	Build the data set to group into time slice
*/
Select datep
		,timepart
		,Row_Number() OVER(PARTITION BY datep order by timepart) Modulous
		,count(histId) TotalCalls
into #prepivot
from Cte_Interval inter
	LEFT JOIN #Hist hist on hist.CallDateTime >=inter.StartTime
								and hist.CallDateTime<inter.EndTime
group by timepart,datep
order by datep,timepart


Anyway time to sign off for the day, if anyone can suggest improvements then please post them.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1421726
bquintana
Posted Tuesday, February 19, 2013 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:37 PM
Points: 4, Visits: 12
ok, so I used this query-


DECLARE @strColumns VARCHAR(MAX)
DECLARE @strSQL VARCHAR(MAX)

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL
DROP TABLE #tmp_table

CREATE TABLE #tmp_table
(
CallDateTime DATETIME,
HistID INT
)

INSERT #tmp_table( CallDateTime, HistID )
Select' 11/1/2012 9:02:30.000', 2270647 UNION ALL
Select' 11/1/2012 8:59:59.000', 2270648 UNION ALL
Select' 11/1/2012 9:01:33.000', 2270650 UNION ALL
Select' 11/1/2012 9:03:40.000', 2270652 UNION ALL
Select' 11/1/2012 9:02:50.000', 2270653 UNION ALL
Select' 11/1/2012 9:06:31.000', 2270661 UNION ALL
Select' 11/1/2012 9:31:08.000', 2270706 UNION ALL
Select' 11/1/2012 9:40:24.000', 2270719

SELECT @strColumns = COALESCE( @strColumns + .000,',.000', '' )
+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) < 30 THEN 1 ELSE 0 END ) AS ['
+ CAST( 8 + Numbers.number AS VARCHAR(3) ) + '_' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + '30], ' + CHAR(13)
+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) >= 30 THEN 1 ELSE 0 END ) AS ['
+ CAST( 830 + Numbers.number * 100 AS VARCHAR(4) ) + '_' + CAST( 9 + Numbers.number AS VARCHAR(3) ) + ']' + CHAR(13)
FROM (
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number < 12
) Numbers --===You can use a Tally table instead of this if you have one

SET @strSQL = ' SELECT CONVERT(VARCHAR(20), CallDateTime, 110) AS Date, '
+ @strColumns
+ ' FROM #tmp_table '
+ ' GROUP BY CONVERT(VARCHAR(20), CallDateTime, 110) '

EXECUTE( @strSQL )

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL
DROP TABLE #tmp_table

__
I get an error messsage

Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.

Post #1421865
Jeff Moden
Posted Tuesday, February 19, 2013 4:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792
bquintana (2/19/2013)
ok, so I used this query-


DECLARE @strColumns VARCHAR(MAX)
DECLARE @strSQL VARCHAR(MAX)

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL
DROP TABLE #tmp_table

CREATE TABLE #tmp_table
(
CallDateTime DATETIME,
HistID INT
)

INSERT #tmp_table( CallDateTime, HistID )
Select' 11/1/2012 9:02:30.000', 2270647 UNION ALL
Select' 11/1/2012 8:59:59.000', 2270648 UNION ALL
Select' 11/1/2012 9:01:33.000', 2270650 UNION ALL
Select' 11/1/2012 9:03:40.000', 2270652 UNION ALL
Select' 11/1/2012 9:02:50.000', 2270653 UNION ALL
Select' 11/1/2012 9:06:31.000', 2270661 UNION ALL
Select' 11/1/2012 9:31:08.000', 2270706 UNION ALL
Select' 11/1/2012 9:40:24.000', 2270719

SELECT @strColumns = COALESCE( @strColumns + .000,',.000', '' )
+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) < 30 THEN 1 ELSE 0 END ) AS ['
+ CAST( 8 + Numbers.number AS VARCHAR(3) ) + '_' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + '30], ' + CHAR(13)
+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) >= 30 THEN 1 ELSE 0 END ) AS ['
+ CAST( 830 + Numbers.number * 100 AS VARCHAR(4) ) + '_' + CAST( 9 + Numbers.number AS VARCHAR(3) ) + ']' + CHAR(13)
FROM (
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number < 12
) Numbers --===You can use a Tally table instead of this if you have one

SET @strSQL = ' SELECT CONVERT(VARCHAR(20), CallDateTime, 110) AS Date, '
+ @strColumns
+ ' FROM #tmp_table '
+ ' GROUP BY CONVERT(VARCHAR(20), CallDateTime, 110) '

EXECUTE( @strSQL )

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL
DROP TABLE #tmp_table

__
I get an error messsage

Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.



So, instead of executing @strSQL, print it out and execute that to figure out what you did wrong.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1421891
bquintana
Posted Wednesday, February 20, 2013 1:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:37 PM
Points: 4, Visits: 12
Got it. Thanks guys for your help.
Post #1422289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.