Help with outer join

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?

Big O,

Have you considered using a DateTime field in your table instead of separating Year and Month like this? This would make the date functions, e.g. datepart(), dateadd(), datediff(), more accessible to you.

If I add a field to your table -- let's call it AccountDate -- I can get the results your after using these functions:

Code Snippet

select

a.Account_No,

b.CurrentAmount,

c.YTDAmount,

d.PriorYTDAmount,

e.PriorAmount

from (

select distinct

Account_No

from FinancialValues

) a

left outer join (

select -- CURRENT AMOUNT BY ACCOUNT

Account_No,

Amount as CurrentAmount

from FinancialValues

where AccountDate = dateadd( dd, -1 * datepart(dd,getdate()) + 1, getdate())

) b

on a.Account_No=b.Account_No

left outer join (

select -- YEAR TO DATE AMOUNT BY ACCOUNT

Account_No,

SUM(Amount) as YTDAmount

from FinancialValues

where year(AccountDate) = year(getdate())

group by Account_No

) c

on a.Account_No=c.Account_No

left outer join (

select -- PRIOR YEAR TO DATE AMOUNT BY ACCOUNT

Account_No,

SUM(Amount) as PriorYTDAmount

from FinancialValues

where year(AccountDate) = year(getdate()) - 1 AND

AccountDate <= dateadd( yy, -1, getdate())

group by Account_No

) d

on a.Account_No=d.Account_No

left outer join (

select -- PRIOR YEAR AMOUNT BY ACCOUNT

Account_No,

Amount as PriorAmount

from FinancialValues

where AccountDate = dateadd(yy, - 1, dateadd( dd, -1 * datepart(dd,getdate()) + 1, getdate()))

) e

on a.Account_No=e.Account_No

It's not the prettiest thing, but it's relatively straightforward. Each value is calculated in a nested subquery. A list of all accounts is generated in the first subquery and these sets of calculated values are joined to it.

Since this is a beginner's forum, I'd generally recommend that you avoid heavy use of temporary tables. If you find yourself creating these to store intermediate data sets, challenge yourself to use nested queries. The performance will be better (up to a point) and this will help you become comfortable with more and more complex SQL problems.

Bryan

PS The code above has not been properly tested. You may need to tweak a few things to make this work exactly as you need.