Calc monthly totals along with yearly totals

Question

Calc monthly totals along with yearly totals

kknobloch

SSC Veteran

Points: 267
More actions
April 2, 2009 at 7:03 am

#198752

I have the following query:
SELECT LEFT(GL_ACCT_ID,2) as FundCode
,RIGHT(GL_ACCT_ID,5) as ObjectCode
,FISCAL_YEAR_MONTH
,SUM(MDEBITS) - SUM(MCREDITS) as CurrentMTDActual
FROM [dbo].[ODS_GL_TRANSACTION_SUMM]
WHERE LEFT(GL_ACCT_ID,2) IN ('11','22','27')
and SUBSTRING(GL_ACCT_ID,16,1) IN ('5','6','7')
and FISCAL_YEAR = 2009
GROUP BY LEFT(GL_ACCT_ID,2), RIGHT(GL_ACCT_ID,5), FISCAL_YEAR_MONTH
ORDER BY LEFT(GL_ACCT_ID,2), RIGHT(GL_ACCT_ID,5), FISCAL_YEAR_MONTH
It retuns the following:
FundCodeObjectCodeFISCAL_YEAR_MONTHMTDActual
11 52100 1 51042.93
11 52100 2 57788.74
11 52100 3 75570.63
I would like to be able to get an additional column of data - "CurrentYTDActual" - any idea how I can get the following:
FundCodeObjectCodeFISCAL_YEAR_MONTHMTDActual YTDActual
11 52100 1 51042.93 51042.93
11 52100 2 57788.74 108831.67
11 52100 3 75570.63 184402.3

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 1

Hello,

I could not actually understand what you are expecting but you can review to the new Grouping Sets improvement in SQL2008 or for using Rollup and Cube in your groupings.

You can review to Group By Grouping Sets - MS SQL Server 2008 T-SQL Improvements and CUBE and ROLLUP articles.

I hope these may help you,

Eralper

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Here's the obvious way, using a correlated subquery. Performance will drop dramatically with increasing rowcount.

-- make some sample data

DROP TABLE #ODS_GL_TRANSACTION_SUMM

CREATE TABLE #ODS_GL_TRANSACTION_SUMM (GL_ACCT_ID VARCHAR(16), FISCAL_YEAR_MONTH INT, FISCAL_YEAR INT, MDEBITS MONEY, MCREDITS MONEY)

INSERT INTO #ODS_GL_TRANSACTION_SUMM (GL_ACCT_ID, FISCAL_YEAR_MONTH, FISCAL_YEAR, MDEBITS, MCREDITS)

SELECT '1152100 5', 1, 2009, 51042.93, 0 UNION ALL

SELECT '1152100 5', 2, 2009, 57788.74, 0 UNION ALL

SELECT '1152100 5', 3, 2009, 75570.63, 0

-- run the query

SELECT LEFT(GL_ACCT_ID,2) as FundCode

,RIGHT(GL_ACCT_ID,5) as ObjectCode

,FISCAL_YEAR_MONTH

,SUM(MDEBITS) - SUM(MCREDITS) as CurrentMTDActual

,(SELECT SUM(MDEBITS) - SUM(MCREDITS)

FROM #ODS_GL_TRANSACTION_SUMM

WHERE LEFT(GL_ACCT_ID,2) IN ('11','22','27')

and SUBSTRING(GL_ACCT_ID,16,1) IN ('5','6','7')

and FISCAL_YEAR = 2009

AND FISCAL_YEAR_MONTH <= s.FISCAL_YEAR_MONTH) AS CurrentYTDActual

FROM #ODS_GL_TRANSACTION_SUMM s

WHERE LEFT(GL_ACCT_ID,2) IN ('11','22','27')

and SUBSTRING(GL_ACCT_ID,16,1) IN ('5','6','7')

and FISCAL_YEAR = 2009

GROUP BY LEFT(GL_ACCT_ID,2), RIGHT(GL_ACCT_ID,5), FISCAL_YEAR_MONTH

ORDER BY LEFT(GL_ACCT_ID,2), RIGHT(GL_ACCT_ID,5), FISCAL_YEAR_MONTH

Is this Lawson, by any chance?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden