February 18, 2013 at 3:25 pm
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
February 18, 2013 at 4:28 pm
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
Change is inevitable... Change for the better is not.
February 19, 2013 at 7:36 am
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.....
February 19, 2013 at 7:52 am
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
(
CallDateTimeDATETIME,
HistIDINT
)
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(
SELECTnumber
FROMmaster..spt_values
WHEREtype = 'P' AND number < 12
) Numbers --===You can use a Tally table instead of this if you have one
SET@strSQL = ' SELECTCONVERT(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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 19, 2013 at 7:52 am
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
February 19, 2013 at 9:13 am
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
February 19, 2013 at 9:47 am
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
February 19, 2013 at 2:59 pm
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
(
CallDateTimeDATETIME,
HistIDINT
)
INSERT #tmp_table( CallDateTime, HistID )
Select'11/1/2012 9:02:30.000',2270647UNION ALL
Select'11/1/2012 8:59:59.000',2270648UNION ALL
Select'11/1/2012 9:01:33.000',2270650UNION ALL
Select'11/1/2012 9:03:40.000',2270652UNION ALL
Select'11/1/2012 9:02:50.000',2270653UNION ALL
Select'11/1/2012 9:06:31.000',2270661UNION ALL
Select'11/1/2012 9:31:08.000',2270706UNION 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(
SELECTnumber
FROMmaster..spt_values
WHEREtype = 'P' AND number < 12
) Numbers --===You can use a Tally table instead of this if you have one
SET@strSQL = ' SELECTCONVERT(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.
February 19, 2013 at 4:49 pm
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
(
CallDateTimeDATETIME,
HistIDINT
)
INSERT #tmp_table( CallDateTime, HistID )
Select'11/1/2012 9:02:30.000',2270647UNION ALL
Select'11/1/2012 8:59:59.000',2270648UNION ALL
Select'11/1/2012 9:01:33.000',2270650UNION ALL
Select'11/1/2012 9:03:40.000',2270652UNION ALL
Select'11/1/2012 9:02:50.000',2270653UNION ALL
Select'11/1/2012 9:06:31.000',2270661UNION ALL
Select'11/1/2012 9:31:08.000',2270706UNION 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(
SELECTnumber
FROMmaster..spt_values
WHEREtype = 'P' AND number < 12
) Numbers --===You can use a Tally table instead of this if you have one
SET@strSQL = ' SELECTCONVERT(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
Change is inevitable... Change for the better is not.
February 20, 2013 at 1:37 pm
Got it. Thanks guys for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply