Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Retrieving Current and Prior Year Total on one row

Author  Topic 

em172967
Starting Member

10 Posts

Posted - 2014-10-24 : 17:41:23
Note that I am using SQL Server 2008 Business Intelligense Development Studio.


Hi, I have written a union query that gets me Budget, Budget YTD, Actuals Balance YTD, Actual Credits, Actual Debits. It is as follows:

SELECT        SourceID, BudgetID, FiscalYearID, AccountID, PeriodEndDateTime, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode, 
AccountCorporation, AccountCorporationName, AccountDescription, Budget, BudgetYTD, 0 AS Balance, 0 AS Credit, 0 AS Debit, RowUpdateDateTime
FROM GlBudgetsByPeriod
WHERE (FiscalYearID >= 2012)
UNION
SELECT SourceID, 'Actuals' AS BudgetID, YEAR(DateTime) AS Year, AccountID, DateTime, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode,
AccountCorporation, AccountCorporationName, AccountDescription, 0 AS Budget, 0 AS BudgetYTD, Balance, Credit, Debit, RowUpdateDateTime
FROM GlAccountAmounts
WHERE (DateTime >= '20120101')


It works well. But now, I would like to add Prior Year Credits, Prior Year Debits and Prior Year Balance YTD.

SELECT        SourceID, BudgetID, FiscalYearID, AccountID, PeriodEndDateTime, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode, 
AccountCorporation, AccountCorporationName, AccountDescription, Budget, BudgetYTD, 0 AS Balance, 0 AS Credit, 0 AS Debit, RowUpdateDateTime, DATEADD(year,
- 1, PeriodEndDateTime) AS Prior_Year, 0 AS PY_Balance, 0 AS PY_Credit, 0 AS PY_Debit
FROM GlBudgetsByPeriod AS GlBudgetsByPeriod_1
WHERE (FiscalYearID >= 2012)
UNION
SELECT SourceID, 'Actuals' AS BudgetID, YEAR(DateTime) AS Year, AccountID, DateTime, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode,
AccountCorporation, AccountCorporationName, AccountDescription, 0 AS Budget, 0 AS BudgetYTD, Balance, Credit, Debit, RowUpdateDateTime, DATEADD(year, - 1,
DateTime) AS Prior_Year,
(SELECT Balance
FROM GlAccountAmounts
WHERE (DateTime = DATEADD(year, - 1, DateTime))) AS PY_Balance,
(SELECT Debit
FROM GlAccountAmounts AS GlAccountAmounts_3
WHERE (DateTime = DATEADD(year, - 1, DateTime))) AS PY_Debit,
(SELECT Credit
FROM GlAccountAmounts AS GlAccountAmounts_2
WHERE (DateTime = DATEADD(year, - 1, DateTime))) AS PY_Credit
FROM GlAccountAmounts AS GlAccountAmounts_1
WHERE (DateTime >= '20120101')



I see why it doesn't work, it's because Datetime in a row will never = Date - 1 year.
What I don't see is how to retrieve the values for 1 year ago. I tried using rowcount but BIDS says the Over clause is not allowed, so i am fairly certain if that is an option it won't work.

em172967
Starting Member

10 Posts

Posted - 2014-10-24 : 18:30:48
I have to test it...but this may have done it. I'll keep you posted!

SELECT        SourceID, BudgetID, FiscalYearID, AccountID, PeriodEndDateTime, AccountClass, AccountClassType, AccountControlAccount, AccountControlCode, 
AccountCorporation, AccountCorporationName, AccountDescription, Budget, BudgetYTD, 0 AS Balance, 0 AS Credit, 0 AS Debit, RowUpdateDateTime, DATEADD(year,
- 1, PeriodEndDateTime) AS Prior_Year, 0 AS py_bal, 0 AS py_debit, 0 AS py_credit
FROM GlBudgetsByPeriod AS GlBudgetsByPeriod_1
WHERE (FiscalYearID >= 2012)
UNION
SELECT GlAccountAmounts_1.SourceID, 'Actuals' AS BudgetID, YEAR(GlAccountAmounts_1.DateTime) AS Year, GlAccountAmounts_1.AccountID,
GlAccountAmounts_1.DateTime, GlAccountAmounts_1.AccountClass, GlAccountAmounts_1.AccountClassType, GlAccountAmounts_1.AccountControlAccount,
GlAccountAmounts_1.AccountControlCode, GlAccountAmounts_1.AccountCorporation, GlAccountAmounts_1.AccountCorporationName,
GlAccountAmounts_1.AccountDescription, 0 AS Budget, 0 AS BudgetYTD, GlAccountAmounts_1.Balance, GlAccountAmounts_1.Credit, GlAccountAmounts_1.Debit,
GlAccountAmounts_1.RowUpdateDateTime, DATEADD(year, - 1, GlAccountAmounts_1.DateTime) AS Prior_Year, PY.py_bal, PY.py_debit, PY.py_credit
FROM GlAccountAmounts AS GlAccountAmounts_1 LEFT OUTER JOIN
(SELECT Balance AS py_bal, Debit AS py_debit, Credit AS py_credit, AccountID, DATEADD(Year, - 1, DateTime) AS EXPR1
FROM GlAccountAmounts) AS PY ON PY.AccountID = GlAccountAmounts_1.AccountID AND GlAccountAmounts_1.DateTime = PY.EXPR1
WHERE (GlAccountAmounts_1.DateTime >= '20120101')
Go to Top of Page
   

- Advertisement -