Filling hourly time spans in a result set

Question

At some hours, I insert data into the database with a timestamp. I want to get an hourly view with rows without records filled with data from the last record:

Table

  Datetime value  
 12 4  
 15 6  

Desired Result

  Datetime value  
 12 4  
 13 4  
 14 4  
 15 6  
 16 6  

It should be possible, but I can’t think of any effective way.

How can I do that?

Answer 1

OK, incorrectly analyzing the question and providing an answer that effectively closes the gaps; in order to get the last value in sql, you will need to loop data that is less efficient.

Create Table tblMyValues (MyHours int, MyValue int) Insert into tblMyValues select 12, 4 Insert into tblMyValues select 15, 6 Insert into tblMyValues select 15, 6 Create Table tblResult (MyHour int, ReportValue Int) Declare @i Int = 0, @LastValue Int While @i <= 24 Begin select @i = @i + 1 Select @LastValue = coalesce(MyValue, @LastValue) From tblMyValues Where MyHours = @i insert into tblResult Select @i, @LastValue End select * from tblResult drop table tblMyValues drop table tblResult 

It returns

 MyHour ReportValue ----------- ----------- 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL 12 4 13 4 14 4 15 6 16 6 17 6 18 6 19 6 20 6 21 6 22 6 23 6 24 6 

Answer 2

This requires a second table. It will have a row for each hourly timestamp, regardless of whether there are rows for these timestamps for the table that you described.

Say your table:

CREATE TABLE [dbo].[TimeStamp]( [DateTimeHour] [int] NULL, [Value] [int] NULL )

And a new table:

CREATE TABLE [dbo].[DateTimeTally]( [DateTimeHour] [int] NULL )

Insert rows into the TimeStamp table and rows 0-23 into the DateTimeTally table. So the final request:

 SELECT DateTimeHour, ISNULL((SELECT VALUE FROM TimeStamp ST1 WHERE ST1.DateTimeHour = (SELECT MAX(DateTimeHour)FROM TimeStamp ST2 WHERE ST2.DateTimeHour <= A.DateTimeHour)),0) Value --Not equal, actually less than or equal FROM (SELECT D.DateTimeHour DateTimeHour,T.Value Value FROM TimeStamp T RIGHT OUTER JOIN DateTimeTally D ON(T.DateTimeHour = D.DateTimeHour))A 

This returns:

 DateTimeHour Value ----------- ----------- 0 0 1 0 2 0 3 0 4 0 5 0 6 0 7 0 8 0 9 0 10 0 11 0 12 4 13 4 14 4 15 6 16 6 17 6 18 6 19 6 20 6 21 6 22 6 23 6 

Hope that helps :)