Smoothing the result by date
Using SQL, I need to return a smooth result set (i.e., one per day) from a dataset that contains 0-N records per day.
The result per day must be the most recent previous value, even if it is not the same day. For example:
Initial data:
Date: Time: Value
19/3/2014 10:01 5
19/3/2014 11:08 3
19/3/2014 17:19 6
20/3/2014 09:11 4
22/3/2014 14:01 5
Desired output:
Date: Value
19/3/2014 6
20/3/2014 4
21/3/2014 4
22/3/2014 5
source to share
First you need to fill in the date range and fill in the missing dates ( 21/3/2014
in your example). This can be done by concatenating the calendar table, if present, or by using a recursive general table expression to generate the complete sequence on the fly.
When you have a complete sequence of dates defining the maximum value for a date, or from the last previous nonzero line, it becomes easy. In this query, I am using a correlated subquery to do this.
with cte as (
select min(date) date, max(date) max_date from your_table
union all
select dateadd(day, 1, date) date, max_date
from cte
where date < max_date
)
select
c.date,
(
select top 1 max(value) from your_table
where date <= c.date group by date order by date desc
) value
from cte c
order by c.date;
source to share