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.
