title: Having trouble with recursive SQL over time
body: <p>I'm rewriting one of our inhouse financial systems. What I'm setting up is that this new system takes a snapshot of the order table every month and date that snapshot. This part works great. <br></p>
  
  <p>Next step is that I create a view that will contain the totals for every month, and the totals for it's previous month.</p>
  
  <p>When I do a total by team and month, I have no problem setting up the recursion. I just do a natural join on team, and month=month-1 (I setup a separate table for the month with an identity to make life easier)</p>
  
  <p>Where I'm running into problems is that if I try to do the recursion at the same time as I do the join. I'm joining on team and month again, but I'm ending up with a cross join for some reason. Is there an easy reason to see what's causing the cross join? And does anyone see an error in my logic?
  Here's the join:</p>
  
  <pre><code>select SH1.month, SH1.Team,
     sum(SH2.Monthly_SAMT) as BilledPrevious, sum(SH1.Monthly_SAMT) as BilledCurrent,
     sum(SH1.Monthly_SAMT) - sum(SH2.MonthlyAnnualized_SAMT) BILLEDDIFF
  from vw_SalesMonthly2 SH1 full outer join vw_SalesMonthly2 SH2
     on SH1.team=SH2.team and SH1.month_index-1 = SH2.month_index
  group by SH1.month, SH1.Team
  order by 1 desc, 2 asc
  </code></pre>