1

I want to select max, min, avg value within each 1 day range.

My Table looks like:

+-------+---------------------+
| Value |      TimeStamp      |
+-------+---------------------+
| 31.20 | 2019-04-20 20:02:51 |
| 31.00 | 2019-04-20 20:17:33 |
| 31.00 | 2019-04-21 20:32:15 |
| 30.70 | 2019-04-21 20:46:58 |
| 30.50 | 2019-04-21 21:01:40 |
| 30.50 | 2019-04-24 21:16:22 |
| 30.20 | 2019-04-24 21:31:04 |
| 30.20 | 2019-04-26 21:45:47 |
| 30.00 | 2019-04-26 22:00:29 |
| 29.70 | 2019-04-26 22:15:11 |
+-------+---------------------+

I want to select max, min, avg value within each 1 day range from 2019-04-20 to 2019-04-25.

I want my output(for avg value) to be like this:

+-------+---------------------+
| Value |      TimeStamp      |
+-------+---------------------+
| 31.10 | 2019-04-20 00:00:00 |
| 30.73 | 2019-04-21 00:00:00 |
| 0     | 2019-04-22 00:00:00 |
| 0     | 2019-04-23 00:00:00 |
| 30.35 | 2019-04-24 00:00:00 |
| 0     | 2019-04-25 00:00:00 |
+-------+---------------------+

And I have used the query below:

SELECT AVG(Value) AS Value,
       DATEADD(day, DATEDIFF(day, 0, timestamp), 0) AS TimeStamp 
FROM MyTable
GROUP BY DATEADD(day, DATEDIFF(day, 0, timestamp), 0)

But What I received like this:

+-------+---------------------+
| Value |      TimeStamp      |
+-------+---------------------+
| 31.10 | 2019-04-20 00:00:00 |
| 30.73 | 2019-04-21 00:00:00 |
| 30.35 | 2019-04-24 00:00:00 |
+-------+---------------------+

Missing rows at TimeStamp: 2019-04-22 , 2019-04-23 and 2019-04-25

How do I resolve that?

AJHope
  • 13
  • 3

1 Answers1

1

you can create a recursive CTE that will result in a table returning all dates between the give dates, and then JOIN this with your result:

WITH abc as 
(
 SELECT CAST('2019-04-20' as date) as i
 UNION ALL
 SELECT DATEADD(d,1,i)
 FROM abc WHERE i < CAST('2019-04-25' as date)
)
SELECT
  ISNULL(AVG(Value),0) AS VALUE,
  CAST(Timestamp AS DATE) AS TimeStamp
FROM abc
LEFT JOIN MyTable on TimeStamp = abc.i
GROUP BY abc.i
Luuk
  • 888
  • 5
  • 12
  • Ok, I corrected the query, changed GROUP BY CAST(Timestamp AS DATE) to GROUP BY abc.i – Luuk Sep 21 '19 at 10:37
  • to test see: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=11d9e72c61ff3ab98629dbbee1f39ea9 – Luuk Sep 21 '19 at 10:41
  • I don't know why all value is 0.00 when TimeStamp's data type is datetime. But It's correct when TimeStamp's data type is char – AJHope Sep 21 '19 at 14:29
  • Please see here: (https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=81233a14b49e25c54272b1bfdd11812c) – AJHope Sep 21 '19 at 14:39
  • The `ISNULL(value,0)' will return 0 when value IS NULL – Luuk Sep 21 '19 at 16:48