sql - How to return value based on the last available timestamp if the exact time is unavailable? -

i trying return data in fifteen minute intervals. first thing thought this:

select * mytable datepart(minute, timestamp) % 15 = 0

but there 2 problems approach. first there not data timestamp @ given minute, other there multiple data points @ given minute different second values. want have 1 row each fifteen minute group, @ :00, :15, :30, etc.

this data recorded when changes, if don't have data point @ 12:30, example, take closest data point before , use value 12:30 , correct.

so need able return timestamps @ :00, :30, etc along data record closest time.

the data span years more shorter amount of time, days or weeks. expected output like:

timestamp               value 1/1/2015 12:30:00       25 1/1/2015 12:45:00       41 1/1/2015 1:00:00        45 

i'm having trouble thinking of way in sql. possible?

given fixed start time, need table of numbers add intervals to. if don't have table of numbers (which useful) quick way generate 1 on fly is

with n1 (select n (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (n)), n2 (n) (select 1 n1 n1 cross join n1 n2), numbers (n) (select row_number() over(order n1.n) n2 n1 cross join n2 n2) select * numbers; 

this generates sequence 1 10,000. more reading on see following series:

• generate set or sequence without loops – part 1
• generate set or sequence without loops – part 2
• generate set or sequence without loops – part 3

then once have numbers can generate intervals:

declare @startdatetime smalldatetime = '20150714 14:00',         @enddatetime smalldatetime = '20150715 15:00';  n1 (select n (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (n)), n2 (n) (select 1 n1 n1 cross join n1 n2), numbers (n) (select row_number() over(order n1.n) n2 n1 cross join n2 n2)  select  interval = dateadd(minute, 15 * (n - 1), @startdatetime)    numbers   dateadd(minute, 15 * (n - 1), @startdatetime) <= @enddatetime 

which gives like:

interval ---------------------- 2015-07-14 14:00:00 2015-07-14 14:15:00 2015-07-14 14:30:00 2015-07-14 14:45:00 2015-07-14 15:00:00 2015-07-14 15:15:00 2015-07-14 15:30:00 

then need find closest value on or before each interval using apply , top:'

/***************************************************************** sample data *****************************************************************/  declare @t table ([timestamp] datetime, value int); insert @t ([timestamp], value) select  dateadd(second, rand(checksum(newid())) * -100000, getdate()),         ceiling(rand(checksum(newid())) * 100) sys.all_objects;  /***************************************************************** query *****************************************************************/  declare @startdatetime smalldatetime = '20150714 14:00',         @enddatetime smalldatetime = '20150715 15:00';  n1 (select n (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (n)), n2 (n) (select 1 n1 n1 cross join n1 n2), numbers (n) (select row_number() over(order n1.n) n2 n1 cross join n2 n2), intervals (   select  interval = dateadd(minute, 15 * (n - 1), @startdatetime)        numbers       dateadd(minute, 15 * (n - 1), @startdatetime) <= @enddatetime ) select  i.interval, t.[timestamp], t.value    intervals         outer apply         (   select  top 1 t.[timestamp], t.value                @t t               t.[timestamp] <= i.interval             order t.[timestamp] desc, t.value         ) t order i.interval; 

---

edit

one point note in case of having 2 equal timestamps both on or closest interval, have applied secondary level of ordering value:

select  i.interval, t.[timestamp], t.value    intervals         outer apply         (   select  top 1 t.[timestamp], t.value                @t t               t.[timestamp] <= i.interval             order t.[timestamp] desc, t.value --- ordering here         ) t order i.interval; 

this arbitrary , chose, advisable ensure order enough items ensure results deterministic, say, if ran query on same data many times same results returned because there 1 row satisfies criteria. if had 2 rows this:

    timestamp    |  value  | field1 -----------------+---------+-------- 2015-07-14 14:00 |   100   |   1 2015-07-14 14:00 |   100   |   2 2015-07-14 14:00 |   50    |   2 

if order timestamp, interval 2015-07-14 14:00, don't know whether value of 50 or 100, , different between executions depending on statistics , execution plan. if order timestamp , value, don't know whether field1 1 or 2.