SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How do I add a rolling sum to a Pivot?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blade Hunter
Starting Member

Australia
6 Posts

Posted - 03/20/2012 :  20:59:06  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/20/2012 :  21:09:29  Show Profile  Reply with Quote
how do you want rolling sums to appear?

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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 03/20/2012 :  21:14:39  Show Profile  Reply with Quote
what is a rolling sum ?


KH
Time is always against us

Go to Top of Page

Blade Hunter
Starting Member

Australia
6 Posts

Posted - 03/20/2012 :  21:16:13  Show Profile  Reply with Quote
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
Go to Top of Page

Blade Hunter
Starting Member

Australia
6 Posts

Posted - 03/20/2012 :  21:17:48  Show Profile  Reply with Quote
quote:
Originally posted by khtan

what is a rolling sum ?


KH
Time is always against us





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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 03/20/2012 :  21:26:40  Show Profile  Reply with Quote

; 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



KH
Time is always against us


Edited by - khtan on 03/20/2012 21:27:09
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/20/2012 :  21:32:30  Show Profile  Reply with Quote

;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;


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

Go to Top of Page

Blade Hunter
Starting Member

Australia
6 Posts

Posted - 03/20/2012 :  21:59:27  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000