Just off the top of my head, I'd suggest that you need a date table so you don't have to try and calculate the FY and the date range for every row. Create the date table once then join on it or use it as a lookup table to get the date range.
/* Create some test tables */
IF OBJECT_ID('tempdb..#ArchiveTable') IS NOT NULL
DROP TABLE #ArchiveTable
CREATE TABLE #ArchiveTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[ReportDate] DATETIME NULL,
[Col1] VARCHAR(50) NULL,
PRIMARY KEY (ID))
IF OBJECT_ID('tempdb..#ProdTable') IS NOT NULL
DROP TABLE #ProdTable
CREATE TABLE #ProdTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[ReportDate] DATETIME NULL,
[Col1] VARCHAR(50) NULL,
PRIMARY KEY (ID))
INSERT INTO #ArchiveTable
SELECT '2012-09-06 00:00:00.000','ABCDEFG' UNION
SELECT '2012-09-07 00:00:00.000','ABCDEFG' UNION
SELECT '2012-09-08 00:00:00.000','ABCDEFG'
INSERT INTO #ProdTable
SELECT '2012-10-06 00:00:00.000','ABCDEFG' UNION
SELECT '2012-10-07 00:00:00.000','ABCDEFG' UNION
SELECT '2012-10-08 00:00:00.000','ABCDEFG' UNION
SELECT '2013-02-14 00:00:00.000','ABCDEFG' UNION
SELECT '2013-02-15 00:00:00.000','ABCDEFG' UNION
SELECT '2013-02-16 00:00:00.000','ABCDEFG'
/* End test tables */
/* Create the date table as a permanent table ONCE */
/* I'm using a temp table just for this example */
IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL
DROP TABLE #DateTable
CREATE TABLE #DateTable (
[ReportDate] DATETIME NOT NULL,
[FiscalYearDate] DATETIME NOT NULL,
PRIMARY KEY (ReportDate))
--populate the date table
;WITH cte AS
(SELECT
CAST('2000-10-01' AS DATETIME) AS ReportDate -- go back as far as required
UNION ALL
SELECT ReportDate + 1
FROM cte
WHERE ReportDate + 1 < '2050-10-01'
)
INSERT INTO #DateTable
SELECT
r.ReportDate
,r.FiscalYearDate
FROM
(
SELECT
ReportDate
,(CASE
WHEN MONTH(ReportDate) > 9
THEN CAST(CAST(YEAR(ReportDate) AS CHAR(4)) + '-10-01' AS DATETIME)
ELSE CAST(CAST(YEAR(ReportDate)-1 AS CHAR(4)) + '-10-01' AS DATETIME)
END) AS FiscalYearDate
FROM
cte
) r
OPTION (MAXRECURSION 0)
/* End date table code */
DECLARE
@ReportDate DATETIME
,@FiscalYearDate DATETIME
,@strSQL VARCHAR(8000)
--examples
SET @ReportDate = '2012-09-07 00:00:00.000'
--SET @ReportDate = '2012-10-07 00:00:00.000'
--SET @ReportDate = '2013-02-15 00:00:00.000'
/* Method One - use dynamic SQL to pass in the correct table name to query */
SET @strSQL = '
SELECT
(CASE
WHEN DATEDIFF(MONTH,d.FiscalYearDate,d.ReportDate) <= 6
THEN ''ProdTable''
ELSE ''ArchiveTable''
END) AS dbSource
,s.*
FROM '
+ CASE
WHEN (SELECT DATEDIFF(MONTH,FiscalYearDate,ReportDate) FROM #DateTable WHERE ReportDate = @ReportDate) <= 6
THEN '#ProdTable'
ELSE '#ArchiveTable'
END + '
AS s
INNER JOIN
#DateTable AS d
ON s.ReportDate = d.ReportDate
WHERE
d.ReportDate = CONVERT(DATETIME,'''+CAST(@ReportDate AS VARCHAR(20))+''',101)'
EXEC(@strSQL)
/* Method Two - use an IF statement to direct control to the proper query */
--do a lookup from the date table first
SELECT
@FiscalYearDate = FiscalYearDate
FROM
#DateTable
WHERE
ReportDate = @ReportDate
--then choose which query to run
IF DATEDIFF(MONTH,@FiscalYearDate,@ReportDate) <= 6
BEGIN
SELECT
'ProdTable' AS dbSource
,*
FROM
#ProdTable
WHERE
ReportDate = @ReportDate
END
ELSE
BEGIN
SELECT
'ArchiveTable' AS dbSource
,*
FROM
#ArchiveTable
WHERE
ReportDate = @ReportDate
END