Inserting missing Dates with previous record data

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 580
Sometimes we might have to work for filling gaps between the dates... To fix that kind of issues, i would like to give the below scenario.

/*I have a table like this below and a job runs every hour to insert the data into this table. Sometimes for some reason data doesn't get inserted for few dates in between, in that case I have to insert all the dates that are missing and get the same ProjectID, CurID and Amount from previous date 
My output should be like this

2013-11-11 00:00:00.000 28756 1 34009.27000
2013-11-12 00:00:00.000 28756 1 34009.27000
2013-11-13 00:00:00.000 28756 1 34009.27000
2013-11-14 00:00:00.000 28756 1 34009.27000
2013-11-15 00:00:00.000 28756 1 34009.27000
2013-11-16 00:00:00.000 28756 1 34009.27000
2013-11-17 00:00:00.000 28756 1 34009.27000
2013-11-18 00:00:00.000 28756 1 34009.27000
2013-11-19 00:00:00.000 28756 1 34009.27000
2013-11-20 00:00:00.000 28756 1 34009.27000
2013-11-21 00:00:00.000 28756 1 34009.27000
2013-11-22 00:00:00.000 28756 1 34009.27000
2013-11-23 00:00:00.000 28756 1 34009.27000
2013-11-24 00:00:00.000 28756 1 34009.27000
2013-11-25 00:00:00.000 28756 1 34009.27000
2013-11-26 00:00:00.000 28756 1 34009.27000
2013-11-27 00:00:00.000 28756 1 34009.27000
2013-11-28 00:00:00.000 28756 1 34009.27000
2013-11-29 00:00:00.000 28756 1 34009.27000
2013-11-30 00:00:00.000 28756 1 34009.27000
2013-12-01 00:00:00.000 28756 1 34009.27000
2013-12-02 00:00:00.000 28756 1 34009.27000
2013-12-03 00:00:00.000 28756 1 27597.42000
2013-12-04 00:00:00.000 28756 1 27597.42000
*/

-- Populating Sample Data into a table variable @a
DECLARE @a TABLE(RecordingDate DATETIME, ProjectID INT, CurID INT, Amount MONEY)
INSERT INTO @a SELECT
'2013-11-11 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-12 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-13 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-14 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-15 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-16 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-17 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-18 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-19 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-20 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-21 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-22 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-23 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-24 00:00:00.000',28756,1,34009.27000 union all select
'2013-11-25 00:00:00.000',28756,1,34009.27000 union all select
'2013-12-03 00:00:00.000',28756,1,27597.42000 union all select
'2013-12-04 00:00:00.000',28756,1,27597.42000

-- Actual query to filling the gaps between records
insert into @a(RecordingDate, ProjectID, CurID, Amount)
select DATEADD(dd,v.number,t.RecordingDate),t.ProjectID,t.CurID,t.Amount
from @a t
cross apply (select min(RecordingDate) as nextdate
from @a
where ProjectID = t.ProjectID
and CurID = t.CurID
and RecordingDate > t.RecordingDate
)t1
cross join master..spt_values v
WHERE nextdate > RecordingDate + 1
and v.type='p'
AND v.number > 0
and DATEADD(dd,v.number,RecordingDate) < nextdate

SELECT * FROM @a

Comments or Responses

Login to post response