I have a following data, in this data i have some dates missing like 5/1/2012 , 9-11/1/2012, 18-22/1/2012 concider these are the holidays and there is no record in them what i want is that in these dates, the previous date ALL customers record should be copied or repeated. i need the script for. if there is a difference of one date like 5/1/2012 in that i need all the customers of 4/1/2012 and from 9-11/1/2012 i need the record of 8/1/2012
First of all, you need get the min and max date of your data in variables and create all dates in a table using that variables. Order you fact table by Date and use peek() function to get min and max dates.
After that, you could create a table with all your dates, theres is a lot of examples how to create a master calendar, you can use that to create dates from vMinDate to vMaxDate and do a left join by Date of your fact table with this table that has only dates for everyday:
load * resident
Then, you load the same table but with the next logic, if your numeric fields are null values, you use the value of the previous day, so you use peek() function to get the last value loaded like this:
if(isnull(NumField), peek('NumField'), NumField) as NumField