How to fill in the blanks?

Question

Assuming I have two entries, with both date and count:

--Date-- --Count-- 2011-09-20 00:00:00 5 2011-09-16 00:00:00 8 

How did you choose this to fill in the time intervals, always taking the last previous record?

Thus, the output will be:

 --Date-- --Count-- 2011-09-20 00:00:00 5 2011-09-19 00:00:00 8 2011-09-18 00:00:00 8 2011-09-17 00:00:00 8 2011-09-16 00:00:00 8 

I still could not figure out how to do this. I think this can be done with DATEDIFF and for-loop, but I hope this can be made easier.

Answer 1

You have 2 questions that you are trying to solve. The first question is how to fill in the blanks. The second problem is filling in the Count field for those missing entries.

Problem 1: this can be resolved either using the Dates Lookup table , or by creating a recursive common table expression . I would recommend creating a date lookup table for this if this is an option. If you cannot create such a table, you will need something like this.

 WITH CTE AS ( SELECT MAX(dt) maxdate, MIN(dt) mindate FROM yourtable ), RecursiveCTE AS ( SELECT mindate dtfield FROM CTE UNION ALL SELECT DATEADD(day, 1, dtfield) FROM RecursiveCTE R JOIN CTE T ON R.dtfield < T.maxdate ) 

This should create a list of dates starting with the MIN date in your table and ending with MAX .

Problem 2: this is where the correlated subquery in handy (as far as I usually avoid them) to get the last cnt from your source table:

 SELECT r.dtfield, (SELECT TOP 1 cnt FROM yourtable WHERE dt <= r.dtfield ORDER BY dt DESC) cnt FROM RecursiveCTE r 

• SQL Fiddle Demo

Answer 2

My solution is as follows.

Step 1: Specify a date table that has all the dates. - you can use many ex methods: Get a list of dates between two dates

Step 2: Make the left outside of the date table to your result set. - which will result in the following result set: call this table as "TEST_DATE_COUnt"

 --Date-- --Count-- 2011-09-20 00:00:00 5 2011-09-19 00:00:00 0 2011-09-18 00:00:00 0 2011-09-17 00:00:00 0 2011-09-16 00:00:00 8 

Step 3: Make a recursive query as shown below:

 SELECT t1.date_x, t1.count_x, (case when count_x=0 then (SELECT max(COUNT_X) FROM TEST_DATE_COUNT r WHERE r.DATE_X <= t1.DATE_X) else COUNT_X end) cnt FROM TEST_DATE_COUNT t1 

Please let me know if this works. I tested and worked.