Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count by 30 min interval query Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 3:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:36 PM
Points: 12, Visits: 40
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
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: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1421371
Posted Tuesday, February 19, 2013 7:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:36 PM
Points: 12, Visits: 40
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
Posted Tuesday, February 19, 2013 7:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:37 AM
Points: 2,649, Visits: 4,726
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
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: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
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: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
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: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Posted Tuesday, February 19, 2013 2:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:36 PM
Points: 12, Visits: 40
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
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: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1421891
Posted Wednesday, February 20, 2013 1:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:36 PM
Points: 12, Visits: 40
Got it. Thanks guys for your help.
Post #1422289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse