locked
Group by 5 minutes and show empty rows RRS feed

  • Question

  • Hi!

    I have a table with the following data:

    MYDATETIME                      MYVALUE
    ===========================     ==========
    2017-09-26 16:56:44.0000000	0,3839842
    2017-09-26 16:57:45.0000000	0
    2017-09-26 16:58:45.0000000	0
    2017-09-26 16:59:45.0000000	0
    2017-09-26 17:16:37.0000000	4,999389
    2017-09-26 17:17:37.0000000	0,7685996
    2017-09-26 17:18:37.0000000	0
    2017-09-26 17:19:37.0000000	1,53784
    2017-09-26 17:20:37.0000000	6,53787
    2017-09-26 17:21:37.0000000	9,999418
    2017-09-26 17:22:37.0000000	7,691726
    2017-09-26 17:23:37.0000000	17,69176
    2017-09-26 17:24:38.0000000	5,384004
    2017-09-26 17:25:38.0000000	3,076312
    2017-09-26 17:26:38.0000000	0
    2017-09-26 17:27:38.0000000	7,691716
    2017-09-26 17:28:38.0000000	0
    2017-09-26 17:29:38.0000000	0
    2017-09-26 17:30:38.0000000	5,027356

    And using this query:

    SELECT dateadd(minute, datediff(minute, 0, mydatetime) / 5 * 5, 0) as [Date],
        avg(myvalue) as [Average_Value]
    FROM table
    and mydatetime >= '2017-09-26 16:55:00'
    and mydatetime < dateadd(minute, +35, '2017-09-26 16:55:00')
    GROUP BY dateadd(minute, datediff(minute, 0, mydatetime) / 5 * 5, 0)
    ORDER BY [Date]

    I got the results:

    Date                    Average_Value
    ======================= =============
    2017-09-26 16:55:00.000	0,09599605
    2017-09-26 17:15:00.000	1,82645715
    2017-09-26 17:20:00.000	9,4609556
    2017-09-26 17:25:00.000	2,1536056

    But I need to have this:

    Date                    Average_Value
    ======================= =============
    2017-09-26 16:55:00.000	0,09599605
    2017-09-26 17:00:00.000	NULL
    2017-09-26 17:05:00.000	NULL
    2017-09-26 17:10:00.000	NULL
    2017-09-26 17:15:00.000	1,82645715
    2017-09-26 17:20:00.000	9,4609556
    2017-09-26 17:25:00.000	2,1536056

    Can someone help me?

    Thanks

    Wednesday, September 27, 2017 9:22 PM

All replies

  • You will need a table of numbers to drive this. You can how to create one and a brief introduction here:
    http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum
    (You only need to read down to the header "An inline function.")

    There is an example what is akin to what you want to do, but it goes by full days. But you could use the same technique to create five-minute intervals.

    Wednesday, September 27, 2017 9:51 PM
  • -- Can you try this 
    
    DECLARE @yourtbl AS TABLE( MYDATETIME datetime,                       MYVALUE  numeric(14,10));
    insert into @yourtbl ( MYDATETIME , MYVALUE)
    select '2017-09-26 16:56:44',	0.3839842
    union all select '2017-09-26 16:57:45',0
    union all select '2017-09-26 16:58:45',	0
    union all select '2017-09-26 16:59:45',	0
    union all select '2017-09-26 17:16:37',	4.999389
    union all select '2017-09-26 17:17:37',	0.7685996
    union all select '2017-09-26 17:18:37',	0
    union all select '2017-09-26 17:19:37',	1.53784
    union all select '2017-09-26 17:20:37',	6.53787
    union all select '2017-09-26 17:21:37',	9.999418
    union all select '2017-09-26 17:22:37',	7.691726
    union all select '2017-09-26 17:23:37',	17.69176
    union all select '2017-09-26 17:24:38',	5.384004
    union all select '2017-09-26 17:25:38',	3.076312
    union all select '2017-09-26 17:26:38',	0
    union all select '2017-09-26 17:27:38',	7.691716
    union all select '2017-09-26 17:28:38',	0
    union all select '2017-09-26 17:29:38',	0
    union all select '2017-09-26 17:30:38',	5.027356;
     
    
    
    -- Code from here  
    
    ;With [Dates] AS 
    ( 
        SELECT   CAST(  Convert( Varchar, MIN(MyDateTime) ,112)  AS DATETIME) AS SQLDate ,  DateADD( MI,5, MAX(MyDateTime)) AS max_SQLDate FROM  @yourtbl   
    	UNION ALL
    	SELECT   DateADD( MI,5,SQLDate) , max_SQLDate
    	FROM [Dates] WHERE DATEADD( MI,5,SQLDate)  <= max_SQLDate
    )   
    
    SELECT  [Dates].SQLDate, AVG(b.MYVALUE) AS AvgVal FROM  
    ( 
        SELECT SQLDate , DateADD( MI,5,SQLDate)  nxt5Mnts  FROM  [Dates] WHERE SQLDate >= ( SELECT DateADD( MI,-5, MIN(MyDateTime))   FROM @yourtbl  )
    ) [Dates]
    left JOIN @yourtbl b ON  b.MyDateTime  >= [Dates].SQLDate   AND    b.MyDateTime < [Dates].nxt5Mnts
     GROUP BY  [Dates].SQLDate
    OPTION (MAXRECURSION 0) ;

    • Proposed as answer by Wild.Bill.Work Wednesday, September 27, 2017 11:07 PM
    Wednesday, September 27, 2017 10:42 PM
  • Hi Adnre,

    To achieve your requirement, first we should create a calendar table. Then join your table with this calendar table.

    Please refer to following script, see if it works for you:

    create table #yourtbl ( MYDATETIME datetime,MYVALUE  numeric(14,10));
    insert into #yourtbl ( MYDATETIME , MYVALUE)
    select '2017-09-26 16:56:44',	0.3839842
    union all select '2017-09-26 16:57:45',0
    union all select '2017-09-26 16:58:45',	0
    union all select '2017-09-26 16:59:45',	0
    union all select '2017-09-26 17:16:37',	4.999389
    union all select '2017-09-26 17:17:37',	0.7685996
    union all select '2017-09-26 17:18:37',	0
    union all select '2017-09-26 17:19:37',	1.53784
    union all select '2017-09-26 17:20:37',	6.53787
    union all select '2017-09-26 17:21:37',	9.999418
    union all select '2017-09-26 17:22:37',	7.691726
    union all select '2017-09-26 17:23:37',	17.69176
    union all select '2017-09-26 17:24:38',	5.384004
    union all select '2017-09-26 17:25:38',	3.076312
    union all select '2017-09-26 17:26:38',	0
    union all select '2017-09-26 17:27:38',	7.691716
    union all select '2017-09-26 17:28:38',	0
    union all select '2017-09-26 17:29:38',	0
    union all select '2017-09-26 17:30:38',	5.027356;
    
    select * from #yourtbl
    
    ;With cte as(
    select top 1 dateadd(minute, datediff(minute, 0, mydatetime) / 5 * 5, 0) as [Date]
    from #yourtbl 
    where mydatetime >= '2017-09-26 16:55:00'
    union all 
    select DATEADD(MINUTE,5,Date) as date
    from cte
    where Date < dateadd(minute, +30, '2017-09-26 16:55:00')
    )
    
    select c.Date ,avg(t.MYVALUE) as [Average_Value]
    from cte c
    left join #yourtbl t on c.Date=dateadd(minute, datediff(minute, 0, t.MYDATETIME) / 5 * 5, 0)
    GROUP BY c.Date
    ORDER BY [Date]

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Monday, October 9, 2017 9:36 AM
    Thursday, September 28, 2017 6:31 AM
  • declare  @startdt datetime='2017-09-26 16:55:00'
    declare  @enddt datetime= dateadd(minute, +35, '2017-09-26 16:55:00')
    
    ;with mycte as (
    Select   number
    ,dateadd(minute, number*5+datediff(minute, 0, d.mydatetime) / 5 * 5, 0) [date]
     from master.dbo.spt_values 
    Cross apply (select top 1 MYDATETIME from @yourtbl Order by mydatetime ASC) d
     
     where type='P' 
     and dateadd(minute, number*5+datediff(minute, 0, d.mydatetime) / 5 * 5, 0)<@enddt
     )
     ,maincte as (
     SELECT dateadd(minute, datediff(minute, 0, mydatetime) / 5 * 5, 0) as [Date],
        avg(myvalue) as [Average_Value]
    FROM yourtable
    WHERE mydatetime >= @startdt
    and mydatetime < @enddt
    GROUP BY dateadd(minute, datediff(minute, 0, mydatetime) / 5 * 5, 0)
    
     )
     Select m1.[date],/*ISNULL(m2.Average_Value,0)*/ Average_Value 
     from mycte m1 LEFT JOIN maincte m2 on m1.[date]=m2.[date]


    • Edited by Jingyang Li Thursday, September 28, 2017 4:12 PM
    Thursday, September 28, 2017 4:11 PM