Fill in missing values

Question

Guys I have this table

+--------------------+------+
|stime (datetime)    |svalue|
+--------------------+------+
|1/13/2014 8:40:00 AM|5     |
+--------------------+------+
|1/13/2014 8:45:00 AM|6     |
+--------------------+------+
|1/13/2014 8:46:00 AM|5     |
+--------------------+------+
|1/13/2014 8:50:00 AM|4     |
+--------------------+------+

Would it be possible in mssql to create a query that takes all the data with an interval of 1 minute, and if the date does not exist, it takes the value of the first lower data (WHERE stime <=) and assigns that value to the time

So the result I'm trying to get would look like this:

+--------------------+------+
|stime (datetime)    |svalue|
+--------------------+------+
|1/13/2014 8:40:00 AM|5     |
+--------------------+------+
|1/13/2014 8:41:00 AM|5     |
+--------------------+------+
|1/13/2014 8:42:00 AM|5     |
+--------------------+------+
|1/13/2014 8:43:00 AM|5     |
+--------------------+------+
|1/13/2014 8:44:00 AM|5     |
+--------------------+------+
|1/13/2014 8:45:00 AM|6     |
+--------------------+------+
|1/13/2014 8:46:00 AM|5     |
+--------------------+------+
|1/13/2014 8:47:00 AM|5     |
+--------------------+------+
|1/13/2014 8:48:00 AM|5     |
+--------------------+------+
|1/13/2014 8:49:00 AM|5     |
+--------------------+------+
|1/13/2014 8:50:00 AM|4     |
+--------------------+------+

Thanks in advance!

Answer 1

You can use a CTE to generate time sequence from MIN(stime) to MAX(stime):

WITH TMinMax as
(
   SELECT MIN(stime) as MinTime,
          MAX(stime) as MaxTime
   FROM T
)
,CTE(stime) as 
(
  SELECT MinTime  FROM TMinMax 
  UNION ALL
  SELECT DATEADD(minute,1, stime ) 
  FROM CTE 
     WHERE DATEADD(minute,1, stime )<=
            (SELECT MaxTime from TMinMax)

)
select stime,
       (SELECT TOP 1 svalue 
          FROM T 
          WHERE stime<=CTE.Stime 
        ORDER BY stime DESC) as svalue
from CTE 
ORDER BY stime

SQLFiddle demo

Answer 2

This seems to do the job:

declare @t table (stime datetime,svalue int)
insert into @t(stime,svalue) values
('2014-01-13T08:40:00',5),
('2014-01-13T08:45:00',6),
('2014-01-13T08:46:00',5),
('2014-01-13T08:50:00',4)

;with times as (
    select MIN(stime) as stime,MAX(stime) as etime from @t
    union all
    select DATEADD(minute,1,stime),etime from times where stime < etime
)
select
    t.stime,t_1.svalue
from
    times t
        left join
    @t t_1
        on
            t.stime >= t_1.stime --Find an earlier or equal row
        left join
    @t t_2
        on
            t.stime >= t_2.stime and --Find an earlier or equal row
            t_2.stime > t_1.stime --That's a better match than t_1
where
    t_2.stime is null --Second join fails
order by t.stime
option (maxrecursion 0)

We create a CTE called times that finds all of the minutes between the minimum and maximum stime values. We then attempt two joins back to the original table, with the comments indicating what those two joins are attempting to find. We then, in the WHERE clause, eliminate any rows where the t_2 join succeeded - which are the exact rows where the t_1 join found the best matching row from the table.

option (maxrecursion 0) is just to allow the CTE to be used with a wider range of input values, where generating all of the stime values might require a lot of recursion.