Help joining month/year table to itself over a year's end

[mstrpup]

I am new to SQL and need help with the following problem.

I have a table that contains every Fiscal Period (format is year month) and the corresponding previous Fiscal Period. Here is an example of the data in the table (it really contains all years back to 1998 and out to 2008)…

Existing Table...
Fiscal_Per_ID Last_Fiscal_Per_ID
...
200601 200512
200602 200601
200603 200602
200604 200603
200605 200604
200606 200605
200607 200606
200608 200607
200609 200608
200610 200609
200611 200610
200612 200611
...

Using this table I need to create another table that holds the Fiscal Period ID’s for a rolling ‘Last 3 Months’. Each period needs to be in the table 3 times along with the 3 prior periods, as follows…

Table Needed...
Fiscal_Per_ID Prior_3_Mo_ID
...
200601 200510
200601 200511
200601 200512
200602 200511
200602 200512
200602 200601
200603 200512
200603 200601
200603 200602
200604 200601
200604 200602
200604 200603
200605 200602
200605 200603
200605 200604
200606 200603
200606 200604
200606 200605
200607 200604
200607 200605
200607 200606
200608 200605
200608 200606
200608 200607
200609 200606
200609 200607
200609 200608
200610 200607
200610 200608
200610 200609
200611 200608
200611 200609
200611 200610
200612 200609
200612 200610
200612 200611
...

I have managed to create a SQL statement that works by joining the existing table to itself twice. However, it contains a lot of case statements to make the joins work for the first 3 months of the year where the prior 3 months go back into the previous year. Here is what I have...

select t1.fiscal_period_id,
case
when right(t1.fiscal_period_id,2)=1 then t2.fiscal_period_id
when right(t1.fiscal_period_id,2)=2 then t3.fiscal_period_id
when right(t1.fiscal_period_id,2)=3 then t2.last_fiscal_period_id
else
case when t1.fiscal_period_id <> t2.fiscal_period_id
then t2.fiscal_period_id
else t2.fiscal_period_id -1
end
end
from tfm_last_fiscal_period t1
left outer join tfm_last_fiscal_period t2
on t1.last_fiscal_period_id between t2.fiscal_period_id - 2 and t2.fiscal_period_id + 2
left outer join tfm_last_fiscal_period t3
on t2.last_fiscal_period_id between t3.fiscal_period_id - 1 and t3.fiscal_period_id + 1
where t1.fiscal_period_id <> t3.last_fiscal_period_id
and t1.fiscal_period_id <> t3.fiscal_period_id
and t1.fiscal_period_id <> t2.last_fiscal_period_id
group by t1.fiscal_period_id,
case
when right(t1.fiscal_period_id,2)=1 then t2.fiscal_period_id
when right(t1.fiscal_period_id,2)=2 then t3.fiscal_period_id
when right(t1.fiscal_period_id,2)=3 then t2.last_fiscal_period_id
else
case when t1.fiscal_period_id <> t2.fiscal_period_id
then t2.fiscal_period_id
else t2.fiscal_period_id -1
end
end
order by t1.fiscal_period_id,
case
when right(t1.fiscal_period_id,2)=1 then t2.fiscal_period_id
when right(t1.fiscal_period_id,2)=2 then t3.fiscal_period_id
when right(t1.fiscal_period_id,2)=3 then t2.last_fiscal_period_id
else
case when t1.fiscal_period_id <> t2.fiscal_period_id
then t2.fiscal_period_id
else t2.fiscal_period_id -1
end
end

Once I have this table done, I have to create similar tables for the rolling last 6 months and the rolling last 12 months. So I’m concerned that my joins and case statements will be very ugly indeed.

Any suggestions for making a better sql statement? Perhaps using date functions (just a thought, but I haven’t been able to come up with anything)?

It is necessary to do this in transact-sql as we need to then paste the statement into an extraction program for our data warehouse, which will run the job nightly.

Any help is greatly appreciated!

 

[gmmastros]

I have an idea. It appears as though your original table has 1 record per month for 10 years, so we're talking about 120 records. That's nothing, really.

So, I suggest you create a table variable with an identity field. Insert the fiscal period data in to the table variable, and then join the table on itself to get the prior 3 months.

Here's some code. The @FiscalPeriod table variable exists so I could test the query. You'll want to replace this with your actual table for final impleementation.

[green]-- Create a dummy table to test with[/green]
Declare @FiscalPeriod Table(Fiscal_Per_ID Integer, Last_Fiscal_Per_Id Integer)

Insert Into @FiscalPeriod Values(200601,200512)
Insert Into @FiscalPeriod Values(200602,200601)
Insert Into @FiscalPeriod Values(200603,200602)
Insert Into @FiscalPeriod Values(200604,200603)
Insert Into @FiscalPeriod Values(200605,200604)
Insert Into @FiscalPeriod Values(200606,200605)
Insert Into @FiscalPeriod Values(200607,200606)
Insert Into @FiscalPeriod Values(200608,200607)
Insert Into @FiscalPeriod Values(200609,200608)
Insert Into @FiscalPeriod Values(200610,200609)
Insert Into @FiscalPeriod Values(200611,200610)
Insert Into @FiscalPeriod Values(200612,200611)

[green]-- The actual query starts here[/green]

Declare @Data Table(RowId Integer Identity(1,1), Fiscal_Per_ID Integer)
Insert Into @Data(Fiscal_Per_ID) 
Select Fiscal_Per_ID
From   @FiscalPeriod 
Order By Fiscal_Per_ID DESC

Select A.Fiscal_Per_Id, 
       B.Fiscal_Per_Id As Last_3_Months
From   @Data A 
       Inner Join @Data B
         On B.RowId Between (A.RowId + 1) And (A.RowId + 3)
Order By A.RowId

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mstrpup]

Thanks!