How about including [both] tables? (I'll stop at this point to include the usual disclaimers: "if the key columns are indexed...", etc.)

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