Running Totals Pain - SQL Server Forums

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)

Running Totals Pain

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2015-03-03 : 13:34:55

I have data that looks like this:

TABLE1
COL1 TOTAL YTD DATE
CAT 1 NULL 2014-01-01
DOG 2 NULL 2014-01-01
CAT 1 NULL 2014-02-01
DOG 3 NULL 2014-02-01
CAT 3 NULL 2014-03-01
DOG 3 NULL 2014-03-01
DOG 3 NULL 2015-01-01
CAT 3 NULL 2015-01-01
DOG 3 NULL 2015-02-01
CAT 1 NULL 2015-02-01

I am having some issues writing a dynamic query that will give me a running total for the year. Any help with this would be amazing.
I looked online for about 2 hours with no luck. My date fields are DATE datatype.

My output should be:

TABLE1
COL1 TOTAL YTD DATE
CAT 1 1 2014-01-01
DOG 2 2 2014-01-01
CAT 1 2 2014-02-01
DOG 3 5 2014-02-01
CAT 3 5 2014-03-01
DOG 3 8 2014-03-01
DOG 3 3 2015-01-01
CAT 3 3 2015-01-01
DOG 3 6 2015-02-01
CAT 1 4 2015-02-01

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-04 : 01:42:58

[code]
;WITH aCTE
AS
(SELECT 'CAT' AS COL1 ,1 AS TOTAL , NULL AS YTD, '2014-01-01' AS [DATE]
UNION ALL SELECT 'DOG', 2, NULL ,'2014-01-01'
UNION ALL SELECT 'CAT', 1, NULL,'2014-02-01'
UNION ALL SELECT 'DOG' ,3, NULL, '2014-02-01'
UNION ALL SELECT 'CAT' ,3, NULL, '2014-03-01'
UNION ALL SELECT 'DOG' ,3, NULL, '2014-03-01'
UNION ALL SELECT 'DOG' ,3, NULL, '2015-01-01'
UNION ALL SELECT 'CAT' ,3, NULL, '2015-01-01'
UNION ALL SELECT 'DOG' ,3, NULL, '2015-02-01'
UNION ALL SELECT 'CAT' ,1, NULL, '2015-02-01')

SELECT
COL1
,TOTAL
,SUM(TOTAL) OVER (PARTITION BY YEAR([DATE]), COL1 ORDER BY [DATE]) AS YTD
,[DATE]
FROM
aCTE
ORDER BY [DATE],COL1
[/code]

output:
[code]
COL1 TOTAL YTD DATE
CAT 1 1 2014-01-01
DOG 2 2 2014-01-01
CAT 1 2 2014-02-01
DOG 3 5 2014-02-01
CAT 3 5 2014-03-01
DOG 3 8 2014-03-01
CAT 3 3 2015-01-01
DOG 3 3 2015-01-01
CAT 1 4 2015-02-01
DOG 3 6 2015-02-01
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-04 : 01:50:00

The previous post works for SQL 2012 and up.
For 2008 :


SELECT
	COL1
	,TOTAL
	,A.TOTAL + ISNULL(B.B_TOTAL,0) AS YTD
	,[DATE]
FROM 
	aCTE AS A
	OUTER APPLY
	(
		SELECT SUM(B.TOTAL) AS B_TOTAL
			FROM aCTE AS B
			WHERE A.COL1= B.COL1
				AND YEAR(A.[DATE])= YEAR(B.[DATE])
				AND B.[DATE]< A.[DATE]
				
	)B
ORDER BY [DATE],COL1

output:


COL1	TOTAL	YTD	DATE
CAT	1	1	2014-01-01
DOG	2	2	2014-01-01
CAT	1	2	2014-02-01
DOG	3	5	2014-02-01
CAT	3	5	2014-03-01
DOG	3	8	2014-03-01
CAT	3	3	2015-01-01
DOG	3	3	2015-01-01
CAT	1	4	2015-02-01
DOG	3	6	2015-02-01

sabinWeb MCP

Johnph
Posting Yak Master

103 Posts

Posted - 2015-03-04 : 09:24:45

Stepson are freaking amazing thank you so much.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-05 : 00:31:46

you are Welcome

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources