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 | +--------------------+------+ |…

Accepted Answer

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

Fill in missing values

Solution

Problem