Count by 30 min interval query

  • 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

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

  • 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


    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/

  • 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

  • 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

  • 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

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

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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