Trying To Get A Series Of Number To Go By Months

if

(datalength

(cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD])

) = 1)

then

(0 ||

cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD]))

else

(cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD]))





Goal:

To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.

2006 01

2006 02

2006 03

Etc……





Status of Problem:

The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:

20061

200610

200611

200612

20062

Etc….

This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says



Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?

So if I enter Year: 2007 Period 3

I should get:

2006 01

2006 02

2006 03

Etc…….

2006 11

2006 12

2007 01

2007 02

2007 03



Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.