1

I need to produce a result including non existent rows for a specific timestamp (in a column value). I have two variables for the start and end dates of the period to consult.

Records table:

SELECT record_at, sensor_value
FROM SomeTable
WHERE record_at BETWEEN '2021-03-12 18:01:00' AND '2021-03-12 18:05:00'
record_at sensor_value
'2021-03-12 18:01:00' 123
'2021-03-12 18:02:00' 200
'2021-03-12 18:03:00' 400
'2021-03-12 18:05:00' 10

I need a query to adding that empty row at 18:04:00 in this case.

Expected query result:

record_at sensor_value
'2021-03-12 18:01:00' 123
'2021-03-12 18:02:00' 200
'2021-03-12 18:03:00' 400
'2021-03-12 18:04:00' NULL
'2021-03-12 18:05:00' 10

If no rows are available same approach:

record_at sensor_value
'2025-08-11 16:06:00' NULL
'2025-08-11 16:07:00' NULL
'2025-08-11 16:08:00' NULL
'2025-08-11 16:09:00' NULL
'2025-08-11 16:10:00' NULL

I can't use master..spt_values. Not supported in this version of SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8)

Thanks in advance!

Nelson Hereveri
  • 11
  • 2
  • 1
    The following post shows an answer that describes how to generate a JOIN table with your timestamps WITHOUT the use of `master..spt_values` (https://stackoverflow.com/questions/1478951/generate-a-resultset-of-incrementing-dates-in-tsql) See the (non-accepted) answer from @OMGPonies with around 45 upvotes -- it starts with "Tthe following uses a recursive CTE (SQL Server 2005+)" – David Tansey Mar 12 '21 at 23:44

0 Answers0