Retrieving Current and Prior Year Total on one row

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')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources