Group by 5 minutes and show empty rows

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

Answer 1

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.

Answer 2

-- 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) ;

Answer 3

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 [e-mail].

Answer 4

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]