How do I add a rolling sum to a Pivot? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How do I add a rolling sum to a Pivot?

Author

Topic

Blade Hunter
Starting Member

6 Posts

Posted - 2012-03-20 : 20:59:06

Hi Guys, thanks for any help you can offer, I made my first Pivot an hour ago and am quite proud of it being I have not used MS SQL in about 10 years :).

However I have hit a snag, I need to not only have my data rolling to the righ in a pivot but I need a rolling sum next to each of those columns.

Here is my current code:


SELECT	D_CREATED_DATE,
		[110] 'STD REG',
		[120] 'FB REG',
		[121] 'TWT REG',
		[210] 'STD LOGIN',
		[220] 'FB LOGIN',
		[221] 'TWT LOGIN',
		[310] 'STD VCH REDEMP',
		[320] 'FB VCH REDEMP',
		[321] 'TWT VCH REDEMP',
		[410] 'STD STR ACC CREATED',
		[420] 'FB STR ACC CREATED',
		[421] 'TWT STR ACC CREATED',
		[510] 'STD DOWNLOAD',
		[520] 'FB DOWNLOAD',
		[521] 'TWT DOWNLOAD'
FROM
		(
			SELECT	(N_ACTIVITY_TYPE*10)+N_SOURCE_TYPE N_ACTIVITY_TYPE,
					convert(DATE,dateadd(HH,-17,D_CREATED),112)  D_CREATED_DATE,
					1 as TransCount
			FROM BanditWarehouseV3.dbo.DSP_USER_ACTIVITY_LOG
			WHERE convert(DATE,dateadd(HH,-17,D_CREATED),112) BETWEEN '2012-03-01' AND '2012-03-31'
		) AS SourceTable
PIVOT(
		count(TransCount)
		FOR N_ACTIVITY_TYPE IN (
			[110],
			[120],
			[121],
			[210],
			[220],
			[221],
			[310],
			[320],
			[321],
			[410],
			[420],
			[421],
			[510],
			[520],
			[521]
		)
) AS PivotTable
order by D_CREATED_DATE;

My output is correct and what I want except for those rolling sum coumns.

Any help you can offer I would be eternaly grateful.

Cheers

Dan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 21:09:29

how do you want rolling sums to appear?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-20 : 21:14:39

what is a rolling sum ?

KH
[spoiler]Time is always against us[/spoiler]

Blade Hunter
Starting Member

6 Posts

Posted - 2012-03-20 : 21:16:13

Hi, Thanks for your reply. So I currently have dates down the left and all my trans types across the top.

To the right of each trans type column I would like a rolling sum for that trans type only.

So currently Col1 is my date then Col2 is TransType1, Col3 is TransType2 and Col4 is TransType3

I would like:

Col1: Date
Col2: TransType1
Col3: Running sum of TransType1
Col4: TransType2
Col5: Running sum of TransType2
Col6: TransType3
Col7: Running sum of TransType3

and so on until I run out of TransTypes.

Thanks again for any input you can provide.

Dan

Blade Hunter
Starting Member

6 Posts

Posted - 2012-03-20 : 21:17:48

quote:
Originally posted by khtan

what is a rolling sum ?

KH
[spoiler]Time is always against us[/spoiler]

A rolling sum so on day 1 it will equal day 1 transactions
On day 2 it will equal day 1 PLUS day 2 transactions
Day 3 will be Day 1 + Day 2 + Day 3 transaction etc.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-20 : 21:26:40

[code]
; with data as
(
< basically your existing query add the line in red >
select row_no = row_number() over (order by D_CREATED_DATE),
D_CREATED_DATE,
. . . .
)
select *
from data d
cross apply
(
select c_STD_REG = sum([STD REG]),
c_FB_REG = sum([FB REG]),
. . .
from data x
where x.row_no <= d.row_no
) c
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 21:32:30

[code]
;With Agg_table
AS
(
SELECT D_CREATED_DATE,
[110] 'STD REG',
[120] 'FB REG',
[121] 'TWT REG',
[210] 'STD LOGIN',
[220] 'FB LOGIN',
[221] 'TWT LOGIN',
[310] 'STD VCH REDEMP',
[320] 'FB VCH REDEMP',
[321] 'TWT VCH REDEMP',
[410] 'STD STR ACC CREATED',
[420] 'FB STR ACC CREATED',
[421] 'TWT STR ACC CREATED',
[510] 'STD DOWNLOAD',
[520] 'FB DOWNLOAD',
[521] 'TWT DOWNLOAD'
FROM
(
SELECT (N_ACTIVITY_TYPE*10)+N_SOURCE_TYPE N_ACTIVITY_TYPE,
convert(DATE,dateadd(HH,-17,D_CREATED),112) D_CREATED_DATE,
1 as TransCount
FROM BanditWarehouseV3.dbo.DSP_USER_ACTIVITY_LOG
WHERE convert(DATE,dateadd(HH,-17,D_CREATED),112) BETWEEN '2012-03-01' AND '2012-03-31'
) AS SourceTable
PIVOT(
count(TransCount)
FOR N_ACTIVITY_TYPE IN (
[110],
[120],
[121],
[210],
[220],
[221],
[310],
[320],
[321],
[410],
[420],
[421],
[510],
[520],
[521]
)
) AS PivotTable
)

SELECT t.D_CREATED_DATE,
t.[110],t1.[110Run],t.[120],t1.[120Run],
... other columns here
FROM Agg_Table t
CROSS APPLY (SELECT SUM([110]) AS [110Run],
SUM([120]) AS [120Run],
SUM([121]) AS [121Run],
SUM([210]) AS [210Run],
SUM([220]) AS [220Run],
SUM([221]) AS [221Run],
SUM([310]) AS [310Run],
SUM([320]) AS [320Run],
SUM([321]) AS [321Run],
SUM([410]) AS [410Run],
SUM([420]) AS [420Run],
SUM([421]) AS [421Run],
SUM([510]) AS [510Run],
SUM([520]) AS [520Run],
SUM([521]) AS [521Run]
FROM Agg_Table
WHERE D_CREATED_DATE<=t.D_CREATED_DATE
)t1
order by t.D_CREATED_DATE;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Blade Hunter
Starting Member

6 Posts

Posted - 2012-03-20 : 21:59:27

Thankyou so much for your help. My final part was to order the columns exactly as needed which I have now done. Code shown below. Once again thanks so much, you have really helped me out of a bind here.


with data as
(
    select row_no = row_number() over (order by D_CREATED_DATE),
        D_CREATED_DATE,
		[310] 'STD VCH REDEMP',
		[510] 'STD DOWNLOAD',
		[410] 'STD STR ACC CREATED',
   		[110] 'STD REG',
		[120] 'FB REG',
		[121] 'TWT REG',
		[210] 'STD LOGIN',
		[220] 'FB LOGIN',
		[221] 'TWT LOGIN'
FROM
		(
			SELECT	(N_ACTIVITY_TYPE*10)+N_SOURCE_TYPE N_ACTIVITY_TYPE,
					convert(DATE,dateadd(HH,-17,D_CREATED),112)  D_CREATED_DATE,
					1 as TransCount
			FROM BanditWarehouseV3.dbo.DSP_USER_ACTIVITY_LOG
			WHERE convert(DATE,dateadd(HH,-17,D_CREATED),112) BETWEEN '2012-03-01' AND '2012-03-31'
		) AS SourceTable
PIVOT(
		count(TransCount)
		FOR N_ACTIVITY_TYPE IN (
			[310],
			[510],
			[410],
			[110],
			[120],
			[121],
			[210],
			[220],
			[221]
		)
) AS PivotTable
)
select	D.D_CREATED_DATE,
		D.[STD VCH REDEMP],
		C.c_STD_VCH_REDEMP,
		D.[STD DOWNLOAD],
		C.c_STD_DOWNLOAD,
		D.[STD STR ACC CREATED],
		C.c_STD_STR_ACC_CREATED,
		D.[STD REG],
		C.c_STD_REG,
		D.[FB REG],
		C.c_FB_REG,
		D.[TWT REG],
		C.c_TWT_REG,
		D.[STD LOGIN],
		C.c_STD_LOGIN,
		D.[FB LOGIN],
		C.c_FB_LOGIN,
		D.[TWT LOGIN],
		C.c_TWT_LOGIN
from   data d
       cross apply
       (
           select 
				  c_STD_VCH_REDEMP = sum([STD VCH REDEMP]),
				  c_STD_DOWNLOAD = sum([STD DOWNLOAD]),
				  c_STD_STR_ACC_CREATED = sum([STD STR ACC CREATED]),
				  c_STD_REG = SUM([STD REG]),
                  c_FB_REG  = SUM([FB REG]),
				  c_TWT_REG = SUM([TWT REG]),
				  c_STD_LOGIN = SUM([STD LOGIN]),
				  c_FB_LOGIN = SUM([FB LOGIN]),
				  c_TWT_LOGIN = SUM([TWT LOGIN])
           from   data x
           where  x.row_no <= d.row_no
       ) c
order by D_CREATED_DATE

Kind regards

Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources