title: How to Determine Values for Missing Months based on Data of Previous Months in T-SQL
body: <p>I have a set of transactions occurring at specific points in time:</p>
  
  <pre><code>CREATE TABLE Transactions (
      TransactionDate Date NOT NULL,
      TransactionValue Integer NOT NULL
  )
  </code></pre>
  
  <p>The data might be:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Month   Value
  1/2009  1
  2/2009  1
  3/2009  2
  4/2009  2
  5/2009  2
  6/2009  3
  </code></pre>
  
  <p>Note how, for each month, we either get the value specified in the transaction, or we get the most recent non-null value.</p>
  
  <p>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.</p>
  
  <p>I'm using SQL Server 2008, so if any of the new features will help, I'd like to hear about it.</p>
  
  <p>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.</p>