Running total qarterly and annually in SQL


set nocount on;

use

tempdb;

if

object_id(‘dbo.table1’) is not null drop table dbo.table1;

CREATE

TABLE table1(A INT NOT NULL,

B

CHAR(2) NOT NULL,

rYear

CHAR(4) NOT NULL,

rMonth

TINYINT NOT NULL,

AMT

INT NOT NULL)

INSERT

INTO table1

SELECT

100,’01’,‘2001’,1,50

UNION

ALL SELECT 100,’01’,‘2001’,2,75

UNION

ALL SELECT 100,’01’,‘2001’,3,50

UNION

ALL SELECT 100,’01’,‘2001’,4,60

UNION

ALL SELECT 100,’01’,‘2001’,5,40

UNION

ALL SELECT 100,’01’,‘2001’,6,60

UNION

ALL SELECT 100,’01’,‘2001’,7,40

UNION

ALL SELECT 100,’01’,‘2001’,8,60

UNION

ALL SELECT 100,’01’,‘2001’,9,40

UNION

ALL SELECT 100,’01’,‘2001’,10,60

UNION

ALL SELECT 100,’01’,‘2001’,11,40

UNION

ALL SELECT 100,’01’,‘2001’,12,60

UNION

ALL SELECT 100,’01’,‘2002’,1,75

UNION

ALL SELECT 100,’01’,‘2002’,2,75

UNION

ALL SELECT 100,’01’,‘2002’,3,50

UNION

ALL SELECT 100,’01’,‘2002’,4,60

UNION

ALL SELECT 100,’01’,‘2002’,5,40

UNION

ALL SELECT 100,’01’,‘2002’,6,60

UNION

ALL SELECT 100,’01’,‘2002’,7,40

UNION

ALL SELECT 100,’01’,‘2002’,8,60

UNION

ALL SELECT 100,’01’,‘2002’,9,40

UNION

ALL SELECT 100,’01’,‘2002’,10,60

UNION

ALL SELECT 100,’01’,‘2002’,11,40

UNION

ALL SELECT 100,’01’,‘2002’,12,60

 

SELECT

a.[A],a.[B],a.[rYear],a.[rMonth],

a

.[AMT],

SUM

(CASE WHEN ((b.[rMonth]1) / 3) = ((a.[rMonth]1) / 3)

AND b.[rMonth] <= a.[rMonth]

AND

a.A = b.A

AND

a.B = b.B

AND

a.rYear = b.rYear THEN b.AMT ELSE NULL END) as qTotal, SUM(b.AMT) as rTotal

FROM

table1 AS a

CROSS

JOIN table1 b

WHERE

a.A = b.A AND a.B = b.B AND a.rYear = b.rYear AND b.[rMonth] <= a.[rMonth]

GROUP

BY a.[A],a.[B],a.[rYear],a.[rMonth],a.[AMT]

ORDER

BY a.[A],a.[B],a.[rYear],a.[rMonth],a.[AMT]

You can find original question and other solution here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=4058819&SiteID=1

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s