sql server - How to Determine Values for Missing Months based on Data of Previous Months in T-SQL

Question

I have a set of transactions occurring at specific points in time:

CREATE TABLE Transactions (
    TransactionDate Date NOT NULL,
    TransactionValue Integer NOT NULL
)

The data might be:

INSERT INTO Transactions (TransactionDate, TransactionValue)
VALUES ('1/1/2009', 1)
INSERT INTO Transactions (TransactionDate, TransactionValue)
VALUES ('3/1/2009', 2)
INSERT INTO Transactions (TransactionDate, TransactionValue)
VALUES ('6/1/2009', 3)

Assuming that the TransactionValue sets some kind of level, I need to know what the level was between the transactions. I need this in the context of a set of T-SQL queries, so it would be best if I could get a result set like this:

Month   Value
1/2009  1
2/2009  1
3/2009  2
4/2009  2
5/2009  2
6/2009  3

Note how, for each month, we either get the value specified in the transaction, or we get the most recent non-null value.

My problem is that I have little idea how to do this! I'm only an "intermediate" level SQL Developer, and I don't remember ever seeing anything like this before. Naturally, I could create the data I want in a program, or using cursors, but I'd like to know if there's a better, set-oriented way to do this.

I'm using SQL Server 2008, so if any of the new features will help, I'd like to hear about it.

P.S. If anyone can think of a better way to state this question, or even a better subject line, I'd greatly appreciate it. It took me quite a while to decide that "spread", while lame, was the best I could come up with. "Smear" sounded worse.

Answer 1

I'd start by building a Numbers table holding sequential integers from 1 to a million or so. They come in really handy once you get the hang of it.

For example, here is how to get the 1st of every month in 2008:

select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
from Numbers
where n <= 12;

Now, you can put that together using OUTER APPLY to find the most recent transaction for each date like so:

with Dates as (
    select firstOfMonth = dateadd( month, n - 1, '1/1/2008')
    from Numbers
    where n <= 12
)
select d.firstOfMonth, t.TransactionValue
from Dates d
outer apply (
    select top 1 TransactionValue
    from Transactions
    where TransactionDate <= d.firstOfMonth
    order by TransactionDate desc
) t;

This should give you what you're looking for, but you might have to Google around a little to find the best way to create the Numbers table.