Help with an outer join problem

I have a table Financial_Values that has the following columns:
Year(pk),
Month (pk),
Account_No (pk),
Amount

The combination year, month & account no varies for each year & month.

I need to create sp or function that creates a result set that has the following columns:

Account_No (pk),
Current Amount,
Prior_Year_Amount
Current YTD_Amount,
Prior_Year_YTD

Because the rows in the Financial_Values (number and values of the Account No) can be

different for the current and prior years, I believe I have to do the following steps

1. Create table #Current_Amount
Year(pk),
Month (pk),
Account_No (pk),
Current_Amount

Insert #Current_Amount
Select Year, Month, Account_No, Amount as Current_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And Financial_Value.Month = @.Current_Month

2. Create table #Current_YTD_Amount
Year(pk),
Month (pk),
Account_No (pk),
Current_YTD_Amount

Insert #Current_Amount
Select Year, Month, Account_No, Amount as Current_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And (Financial_Value.Month >= 1 and <= @.Current_Month)

3. Create table #Current_Values
Year(pk),
Month (pk),
Account_No (pk),
Current_Amount,
Current_YTD_Amount

Insert #Current_Values
Select #Current_Amount.Year,
#Current_Amount.Month,
#Current_Amount.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
From #Current_Amount INNER JOIN #Current_YTD_Amount
On #Current_Amount.Year = #Current_YTD_Amount.Year
And #Current_Amount.Month = #Current_YTD_Amount.Month
And #Current_Amount.Account_No = #Current_YTD_Amount.Account_No

4. Create table #Prior_Year_Amount
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_Amount

Insert #Prior_Year_Amount
Select Year, Month, Account_No, Amount as Prior_Year_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And Financial_Value.Month = @.Current_Month

5. Create table #Prior_Year_YTD_Amount
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_YTD_Amount

Insert #Prior_Year_YTD_Amount
Select Year, Month, Account_No, Amount as Prior_Year_YTD_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And (Financial_Value.Month >= 1 and <= @.Current_Month)

6. Create table #Prior_Year_Values
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_Amount,
Prior_Year_YTD_Amount

Insert #Prior_Year_Values
Select #Prior_Year_Amount.Year,
#Prior_Year_Amount.Month,
#Prior_Year_Amount.Account_No,
#Prior_Year.Current_Amount,
#Prior_Year_YTD_Amount.Current_YTD_Amount
From #Prior_Year_Amount INNER JOIN #Prior_Year_YTD_Amount
On #Prior_Year_Amount.Year = #Prior_Year_YTD_Amount.Year
And #Prior_Year_Amount.Month = #Prior_Year_YTD_Amount.Month
And #Prior_Year_Amount.Account_No = #Prior_Year_YTD_Amount.Account_No

7. Create table #Current_and_Prior_Year_Values
Account_No (pk),
Current_Amount,
Current_YTD_Amount,
Prior_Year_Amount,
Prior_Year_YTD_Amount

Select @.Current_Values_Count = Count(Account_No)

From dbo.tblPFW_Current_Values

Select @.Prior_Year_Values_Count = Count(Account_No)

From dbo.tblPFW_Prior_Year_Values

If @.Current_Values_Count > @.Prior_Year_Values_Count

Insert #Current_and_Prior_Year_Values

Select #Current_Values.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
#Prior_Year_Values.Prior_Year_Amount,
#Prior_Year_YTD_Amount.Prior_Year_YTD_Amount

From #Current_Values RIGHT OUTER JOIN #Prior_Year_Values
On #Current_Values.Year = #Prior_Year_Values.Year
And #Current_Values.Month = #Prior_Year_Values.Month
And #Current_Values.Account_No = #Prior_Year_Values.Account_No

Else

Insert #Current_and_Prior_Year_Values

Select #Prior_Year_Values.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
#Prior_Year_Values.Prior_Year_Amount,
#Prior_Year_YTD_Amount.Prior_Year_YTD_Amount

From #Prior_Year_Values RIGHT OUTER JOIN #Current_Values
On #Prior_Year_Values.Year = #Current_Values.Year
And #Prior_Year_Values.Month = #Current_Values.Month
And #Prior_Year_Values.Account_No = #Current_Values.Account_No

Steps 1 thru 6 are working fine, however when I get to Step 7, my stored procedure fails with

trying to insert into #Current_and_Prior_Year_Values a null value the primary key Account_No.

If I create all the tables not as temporary tables it still fails the same way, however

if I don't run step seven and then run views like the select statements in Step 7

I get the correct results from the views.

Also if a perform an inner join in step seven vs an right outer join, the step does not fail with

the null insert, however I don't the right number of rows (account no)

I quess my question is why would the right outer joins in step 7, run as part of a sp, return

any null Account No values?

Or could anyone suggest a different way to get the result set I need?

BigO,

A right outer join in this case will return everything from the #Current_Values table and only the matching values from the #Prior_Years_Value. For records in the #Current_Values table that do not have a matching record in the #Prior_Years_Value table a NULL will be returned for any #Prior_Years_Value column. So if AccountNo 11112 existed in the #Current_Values table, but didn't exist in the #Prior_Years_Value table a NULL is getting returned for the AccountNo since you are using the #Prior_Years_Value AccountNo field. The error message is occuring, because you have AccountNo as the primary key, so NULL values cannot be inserted into it. A couple suggestions would be to either use the #Current_Values.AccountNo or do a case statement like this:

CASE WHEN #Prior_Years_Value.AccountNo IS NULL THEN #Current_Values.AccountNo ELSE #Prior_Years_Value.AccountNo END

I hope this helps out. Let me know if you have any further questions.

Thanks,

crusso