InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:36:04 GMT20http://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxGot it. Thanks guys for your help.Wed, 20 Feb 2013 13:37:59 GMTbquintanahttp://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspx[quote][b]bquintana (2/19/2013)[/b][hr]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 ALLSelect' 11/1/2012 8:59:59.000', 2270648 UNION ALLSelect' 11/1/2012 9:01:33.000', 2270650 UNION ALLSelect' 11/1/2012 9:03:40.000', 2270652 UNION ALLSelect' 11/1/2012 9:02:50.000', 2270653 UNION ALLSelect' 11/1/2012 9:06:31.000', 2270661 UNION ALLSelect' 11/1/2012 9:31:08.000', 2270706 UNION ALLSelect' 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 [b]Msg 241, Level 16, State 1, Line 13Conversion failed when converting date and/or time from character string. [/b][/quote]So, instead of executing @strSQL, print it out and execute that to figure out what you did wrong.Tue, 19 Feb 2013 16:49:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxok, 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 ALLSelect' 11/1/2012 8:59:59.000', 2270648 UNION ALLSelect' 11/1/2012 9:01:33.000', 2270650 UNION ALLSelect' 11/1/2012 9:03:40.000', 2270652 UNION ALLSelect' 11/1/2012 9:02:50.000', 2270653 UNION ALLSelect' 11/1/2012 9:06:31.000', 2270661 UNION ALLSelect' 11/1/2012 9:31:08.000', 2270706 UNION ALLSelect' 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 [b]Msg 241, Level 16, State 1, Line 13Conversion failed when converting date and/or time from character string. [/b]Tue, 19 Feb 2013 14:59:38 GMTbquintanahttp://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxI'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. [code="sql"]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 CTEAS( 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) TotalCallsinto #prepivotfrom Cte_Interval inter LEFT JOIN #Hist hist on hist.CallDateTime >=inter.StartTime and hist.CallDateTime<inter.EndTimegroup by timepart,dateporder by datep,timepart[/code]Anyway time to sign off for the day, if anyone can suggest improvements then please post them.Tue, 19 Feb 2013 09:47:18 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxI 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.[code="sql"]/* Build working set*/CREATE TABLE #Hist( CallDateTime DateTime ,HistId int)Insert into #HistVALUES ('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 #HistVALUES ('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 CTEAS( 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) TotalCallsinto #prepivotfrom Cte_Interval inter LEFT JOIN #Hist hist on hist.CallDateTime >=inter.StartTime and hist.CallDateTime<inter.EndTimegroup by timepart,datep,rnorder 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 #HistDrop Table #PrePivot[/code]Edited : added comments.Tue, 19 Feb 2013 09:13:33 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxThis is my attempt, it works on a 24 hour widow and im sure theres a better way of doing it[code="sql"]CREATE TABLE #Hist( CallDateTime DateTime ,HistId int)Insert into #HistVALUES ('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 CTEAS( 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.EndTimegroup by StartTime,EndTimeorder by StartTime[/code]Tue, 19 Feb 2013 07:52:13 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxYou can use Dynamic CROSS-TABS to achieve thisYou can have a look at the below mentioned articles for more details on CROSS-TABS[url]http://www.sqlservercentral.com/articles/T-SQL/63681/[/url][url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]Something like below[code="sql"]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 ALLSELECT '2013-02-01 17:02:15.000', 2671057 UNION ALLSELECT '2013-02-01 17:06:41.000', 2671059 UNION ALLSELECT '2013-02-01 17:07:20.000', 2671069 UNION ALLSELECT '2013-02-01 17:22:57.000', 2671068 UNION ALLSELECT '2013-02-01 17:38:15.000', 2671067 UNION ALLSELECT '2013-02-01 17:42:48.000', 2671066 UNION ALLSELECT '2013-02-01 17:53:47.000', 2671065 UNION ALLSELECT '2013-02-01 18:02:37.000', 2671064 UNION ALLSELECT '2013-02-01 08:07:04.000', 2671063 UNION ALLSELECT '2013-02-01 08:11:09.000', 2671062 UNION ALLSELECT '2013-02-01 08:22:12.000', 2671061 UNION ALLSELECT '2013-02-01 08:22:48.000', 2671060 UNION ALLSELECT '2013-02-02 08:22:48.000', 2671060SELECT @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[/code]Edit: Added code as an exampleTue, 19 Feb 2013 07:52:01 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxDay 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 702-02-2012 2 3 4 8etc.....Tue, 19 Feb 2013 07:36:37 GMTbquintanahttp://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspx[quote][b]bquintana (2/18/2013)[/b][hr]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 HistID2013-02-01 06:14:41.000 26710582013-02-01 07:02:15.000 26710572013-02-01 07:06:41.000 26710592013-02-01 07:07:20.000 26710692013-02-01 07:22:57.000 26710682013-02-01 07:38:15.000 26710672013-02-01 07:42:48.000 26710662013-02-01 07:53:47.000 26710652013-02-01 08:02:37.000 26710642013-02-01 08:07:04.000 26710632013-02-01 08:11:09.000 26710622013-02-01 08:22:12.000 26710612013-02-01 08:22:48.000 2671060Results should show something like this 06:00-06:30 106:30-07:00 007:00-07:30 407:30-08:00 308:00-08:30 5Thank you for any insight[/quote]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 ???Mon, 18 Feb 2013 16:28:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1421357-1292-1.aspxI 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 HistID2013-02-01 06:14:41.000 26710582013-02-01 07:02:15.000 26710572013-02-01 07:06:41.000 26710592013-02-01 07:07:20.000 26710692013-02-01 07:22:57.000 26710682013-02-01 07:38:15.000 26710672013-02-01 07:42:48.000 26710662013-02-01 07:53:47.000 26710652013-02-01 08:02:37.000 26710642013-02-01 08:07:04.000 26710632013-02-01 08:11:09.000 26710622013-02-01 08:22:12.000 26710612013-02-01 08:22:48.000 2671060Results should show something like this 06:00-06:30 106:30-07:00 007:00-07:30 407:30-08:00 308:00-08:30 5Thank you for any insightMon, 18 Feb 2013 15:25:22 GMTbquintana