Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
52326 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
17689 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
17689 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
52326 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  
 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.06 seconds. Powered By: Snitz Forums 2000