Full Join on two temporary tables not returning all results SQL Server_问答_开发者

UPDATE: I've included complete code that can be pasted into SSRS, to show the results.

We report on lots of date-centric information and have to produce reports from it. Some of the source data isn't updated in a timely manner but to ensure the reports are neat and can share the same date columns we sometimes use a CTE above the query to generate a list of the last 13 months. One server is still using SQL Server 2000 so CTE's aren't possible. I've tried populating two temporary tables and using a full join but it seems to treat it as an inner join. Here is the representative query:

set DATEFORMAT ymd

IF object_id('tempdb..#EXAMPLE_SOURCE_DATA') is not null 
BEGIN    
  DROP TABLE #EXAMPLE_SOURCE_DATA
END

IF object_id('tempdb..#TEMP_STAGING') is not null 
BEGIN    
  DROP TABLE #TEMP_STAGING
END

IF object_id('tempdb..#TEMP_LAST13MONTHS') is not null 
BEGIN    
  DROP TABLE #TEMP_LAST13MONTHS
END

--This would n开发者_高级运维ormally be real data, but use a temporary for testing/bug fixing
CREATE TABLE #EXAMPLE_SOURCE_DATA(
   Date datetime,
   Actual float,
)

CREATE TABLE #TEMP_STAGING(
   Date datetime,
   Actual float,
)

CREATE TABLE #TEMP_LAST13MONTHS(
   Date13 datetime,
)

INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-06-27 12:34:56', 56.56)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-05-23 12:34:56', 45.45)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-04-11 12:34:56', 23.23)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-03-03 12:34:56', 12.12)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-02-01 12:34:56', 12.12)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-01-23 12:34:56', 45.45)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-04-11 12:34:56', 23.23)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-03-03 12:34:56', 12.12)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-02-01 12:34:56', 67.67)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-01-05 12:34:56', 56.56)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2011-02-01 12:34:56', 67.67)
INSERT INTO #EXAMPLE_SOURCE_DATA VALUES ('2010-12-05 12:34:56', 78.78)

-- Copy data to a temp table but 'flatten' the Date into beginning of month, to allow comparison and join.
INSERT INTO #TEMP_STAGING (Date, Actual)
SELECT 
  CAST(CAST(DATEPART(Year,Date) AS varchar) + '-' + CAST(DATEPART(Month,Date) AS varchar) + CAST('-01 00:00:00' as varchar) AS datetime) AS Date,
  Actual
FROM #EXAMPLE_SOURCE_DATA

--Insert rolling 13 months into a date table
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-1,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-2,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-3,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-4,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-5,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-6,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-7,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-8,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-9,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-10,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-11,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-12,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))
INSERT INTO #TEMP_LAST13MONTHS VALUES (DATEADD(MONTH,-13,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)))

-- DEBUG
--/*
SELECT * FROM #TEMP_LAST13MONTHS ORDER BY Date13 DESC
SELECT * FROM #EXAMPLE_SOURCE_DATA ORDER BY Date DESC
SELECT * FROM #TEMP_STAGING ORDER BY Date DESC
--*/

--This doesn't return values against 2011-07-01 or before 2010-12-01 ??? (Date this 

was written 2011-08)
SELECT
  Date,
  SUM(Actual)
FROM #TEMP_LAST13MONTHS
FULL OUTER JOIN #TEMP_STAGING ON #TEMP_LAST13MONTHS.Date13 = #TEMP_STAGING.Date
GROUP BY Date
ORDER BY Date DESC

You have a where clause explicitly selecting the date column from #TEMP _STAGING, thus effectively filtering out every row from #TEMP_LAST13MONTHS that doesn't have a corresponding row in #TEMP_STAGING

Changing your where clause to

(COALESCE(Date, Date13) BETWEEN @StartOfMonthlyReporting AND @EndOfMonthlyReporting)

should be enough (and using a FULL OUTER JOIN)

Edit

You could use either this

SELECT  [Date]
        , SUM([Actual])
FROM    (       
            SELECT  [Date] = COALESCE(#TEMP_STAGING.Date, #TEMP_LAST13MONTHS.Date13)
                    , [Actual] = COALESCE(Actual, 0)
            FROM    #TEMP_LAST13MONTHS 
                    FULL OUTER JOIN #TEMP_STAGING ON #TEMP_LAST13MONTHS.Date13 = #TEMP_STAGING.Date
        ) q
GROUP BY 
        Date
ORDER BY 
        Date DESC

or this

SELECT  [Date] = COALESCE(#TEMP_STAGING.Date, #TEMP_LAST13MONTHS.Date13)
        , [Actual] = SUM(COALESCE(Actual, 0))
FROM    #TEMP_LAST13MONTHS 
        FULL OUTER JOIN #TEMP_STAGING ON #TEMP_LAST13MONTHS.Date13 = #TEMP_STAGING.Date
GROUP BY
        COALESCE(#TEMP_STAGING.Date, #TEMP_LAST13MONTHS.Date13)
ORDER BY
        1 DESC

to return every row your require. (My personel prefercene is, albeit longer, the first option)

Try this (I've updated the sql a little):

SELECT  Date,
        LEFT(CAST(DATENAME(Month, Date) AS NVARCHAR), 3) + ' ' + CAST(YEAR(Date) AS NVARCHAR) AS MonthYear
FROM    [#TEMP_LAST13MONTHS]
        LEFT OUTER JOIN [#TEMP_STAGING]
            ON [#TEMP_LAST13MONTHS].Date13 = [#TEMP_STAGING].Date
WHERE   Date IS NULL OR (Date BETWEEN @StartOfMonthlyReporting AND @EndOfMonthlyReporting)
ORDER BY Date DESC

I'm sorry I can't be more helpful than to ask you to try the above as I get the following error when running your script: