Fill in the 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     |
+--------------------+------+

Is it possible to create a query in mssql 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 this time value

So, the result I'm trying to get will 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 CTE to generate a 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

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 a better match than t_1
where
    t_2.stime is null --Second join fails
order by t.stime
option (maxrecursion 0)

CTE times, stime. , , . WHERE , t_2, , t_1 .

option (maxrecursion 0) , CTE , stime .

Answer 3

> DECLARE @stime INT
SET @stime= (select min(stime) from table group by stime)
WHILE (@stime <=(select max(stime) from table group by stime))
BEGIN
IF (@stime IN (select stime from table))
    begin 
        select stime,svalue from table where stime = @stime
    end
ELSE
    begin 
        select @stime,svalue from table where stime = (select min(stime) from table group by stime)
    end

SET @stime = @stime + 1
END
GO

Answer 4

use tempdb
go
/* you need to create this table once and fill it with all necesaary dates */
create table dbo.tblSeconds (s datetime)

declare @s datetime='20140101'

WHILE @s<'20140102'
    begin
        insert into dbo.tblSeconds (s) values (@s)

        set @s=DATEADD(SECOND,1,@s)
    end


create table dbo.YourTable (s datetime, strValue int)

insert into dbo.YourTable (s,strValue) values('20140101',0),('20140101 05:00',3),('20140101 07:01',5) 


select s.s, (select top(1) t.strValue from dbo.YourTable t where t.s<=s.s order by t.s desc) strValue
from dbo.tblSeconds s
order by s.s

drop table dbo.tblSeconds
drop table dbo.YourTable