Hi, I have one fact table and one huge history table that goes really deep with scores stored with month end dates.
I am looking for a way to manipulate the month and year of DateChanged in table A along with joins on the acct no to retrieve scores from previous months, resulting in a de-normalized data format as in the result table.
Any ideas?? Thanks!
Table A
Acct DateChanged
-----------------------------------
123 1/12/2008
456 8/25/2008
Table B:
Acct DateUpload Score
-----------------------------------------
123 11/30/2007 620
123 12/31/2007 650
123 01/31/2008 712
123 02/29/2008 800
456 05/31/2008 520
456 06/30/2008 630
456 07/31/2008 680
456 08/31/2008 710
456 09/30/2008 780
456 10/31/2008 786
456 11/30/2008 823
Result Table:
Acct Score2MonthPrior Score1MonthPrior
-------------------------------------------------------
123 620 650
456 630 680