How to get the values for the previous day

[smtest]

I have a crosstab in which i have few days in which there is no activity at all. So i want to put the previous day data(dates which has activities) in those days.
pls advice how i can do that.

example
thats what i have:
      2/1/2007   2/2/2007  2/3/2007  2/4/2007  2/5/2007
x      10                12          13                                       

what i want is
      2/1/2007   2/2/2007  2/3/2007  2/4/2007  2/5/2007
x      10                12          13               13            13


thanks                       

[kmuller]

Have you tried doing a add_days on the current date and subtracting 1?

[larsonr]

Try communicating this problem with your DBA.  It sounds like the data set is to only take in data when data exists.  They could during ETL solve this problem for you, and possibly add an extra column called repeated data.  That way you would know which is valid data and which was just repeated due to null data. 

Its just a thought.  I know as a best practice for us, we do a merge on the past 7 days with repolling data to catch up on errors at the source system or latent data getting to us.  In that scenario it would then overwrite your "repeated data" if new data was ever acquired for that day. 

[blom0344]

You may solve this in the ETL process, depending on what you have in store.
If that does not solve it, you would need a stored procedure to process the data.
You would then loop through the dates and assign value of X to a variable. Use this variable value each time you run into a null value and reset it when running into the next 'real' value..

[MDXpressor]

Try creating an additional query.  In the second query, filter the date to the maximum date where 'x' is not null.  Have the caluclation for 'x' in the first query contain an if...then...else statement to go to the 2nd query if 'x' is null in the first.

[blom0344]

Try creating an additional query.  In the second query, filter the date to the maximum date where 'x' is not null.  Have the caluclation for 'x' in the first query contain an if...then...else statement to go to the 2nd query if 'x' is null in the first.

But what if there are multiple 'gaps' in the data? Or one gap that is not at the end of the range?

[MDXpressor]

But what if there are multiple 'gaps' in the data? Or one gap that is not at the end of the range?

You make a good point.  I was really only looking for the latest date which had a value.