/* 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

/* Create some test tables with data */
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(10,1) NOT NULL,
    [ReportDate] DATETIME NULL,
    [Col1]       VARCHAR(50) NULL,
       PRIMARY KEY (ID))    
    
INSERT INTO #ArchiveTable ([ReportDate], [Col1]) SELECT '2011-09-06','A' 
INSERT INTO #ArchiveTable ([ReportDate], [Col1]) SELECT '2011-09-07','B' 
INSERT INTO #ArchiveTable ([ReportDate], [Col1]) SELECT '2011-09-08','C' 
INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-06','D' 
INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-07','E' 
INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-08','F' 
INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-09','G' 
INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-10','H' 
INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-11','I' 
INSERT INTO #ProdTable ([ReportDate], [Col1]) SELECT '2012-10-12','J' 

SELECT * FROM #ArchiveTable
SELECT * FROM #ProdTable
/* End test tables */
   
/* Set a variable to use for searching */
DECLARE @d DATETIME
SELECT @d = '2011-09-08'

/* "Stand back!  I'm gonna do SQL!" */
SELECT Col1, 'A' AS Location 
   FROM #ArchiveTable
   WHERE [ReportDate] = @d 
UNION
SELECT Col1, 'P' AS Location 
   FROM #ProdTable
   WHERE [ReportDate] = @d 

DROP TABLE #ArchiveTable
DROP TABLE #ProdTable