I have an MS SQL data base which contains values stored with their time stamps. So my result table looks like this:
date value
03.01.2016 11
19.01.2016 22
29.01.2016 33
17.02.2016 44
01.03.2016 55
06.03.2016 66
The time stamps don't really follow much of a pattern. Now, I need to extract weekly data from this: (sampled on Friday, for example)
date value
01.01.2016 11 // friday
08.01.2016 11 // next friday
15.01.2016 11
22.01.2016 22
29.01.2016 33
05.02.2016 33
12.02.2016 33
19.02.2016 44
26.02.2016 44
04.03.2016 55
11.03.2016 66
Is there a reasonable way to do this directly in T-SQL?
I could reformat the result table using a C# or Matlab program, but it seems a bit weird, because I seem to again query the result table...
I found my own solution, which I find more readable. I'm first using a WHILE loop to generate the dates I'm looking for. Then I 'join' these dates to the actual data table using an OUTER APPLY, which looks up 'last value before a specific date'. Here's the code:
-- prepare in-memory table
declare @tbl table ( [date] date, [value] int )
insert into @tbl
values
('2016-01-03','11'),
('2016-01-19','22'),
('2016-01-29','33'),
('2016-02-17','44'),
('2016-03-01','55'),
('2016-03-06','66')
-- query
declare @startDate date='2016-01-01';
declare @endDate date='2016-03-31';
with Fridays as (
select @startDate as fridayDate
union all
select dateadd(day,7,fridayDate) from Fridays where dateadd(day,7,fridayDate)<=@endDate
)
select *
from
Fridays f
outer apply (
select top(1) * from @tbl t
where f.fridayDate >= t.[date]
order by t.[value] desc
) as result
option (maxrecursion 10000)
Gives me:
fridayDate date value
---------- ---------- -----------
2016-01-01 NULL NULL
2016-01-08 2016-01-03 11
2016-01-15 2016-01-03 11
2016-01-22 2016-01-19 22
2016-01-29 2016-01-29 33
2016-02-05 2016-01-29 33
2016-02-12 2016-01-29 33
2016-02-19 2016-02-17 44
2016-02-26 2016-02-17 44
2016-03-04 2016-03-01 55
2016-03-11 2016-03-06 66
2016-03-18 2016-03-06 66
2016-03-25 2016-03-06 66
Thanks for everybody's ideas and support though!
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments