Quarterly Breakdown

Question

Post reply

Quarterly Breakdown

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
January 22, 2008 at 8:11 am

#178410

Here is the scenerio:
I have the follwoing table:
ID MonthTotal QuarterlyTotal EndPeriodDate
1 200 1-30-2008
1 500 2-28-2008
1 300 3-31-2008
1 600 4-30-2008
1 300 5-31-2008
1 200 6-20-2008
What I need for the data to look like is this:
ID MonthTotal QuarterlyTotal EndPeriodDate
1 200 200 1-30-2008
1 500 700 2-28-2008
1 300 1000 3-31-2008
1 600 600 4-30-2008
1 300 900 5-31-2008
1 200 1100 6-20-2008
Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adrienne250 Right there with Babe Points: 737 More actions · Answer 1

create table #temp (ID int, monthTotal int, EndPeriodDate datetime)

insert into #temp

select 1,200, '1/30/08'

union all

select 1, 500, '2/28/08'

union all

select 1, 300, '3/31/08'

union all

select 1, 600, '4/30/08'

union all

select 1, 300, '5/31/08'

union all

select 1, 200, '6/30/08'

select

ID,

MonthTotal,

QuarterlyTotal =

(select sum(monthTotal)

from #temp

where datepart(qq,endPeriodDate) = datepart(qq,t1.endPeriodDate)

and datepart(yyyy,endPeriodDate) = datepart(yyyy,t1.endPeriodDate)

and endPeriodDate <= t1.endPeriodDate),

EndPeriodDate

from #temp t1

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 2

And that, folks, is a Triangular join... works seemingly fine on small groups... cripples CPU's on larger groups... be careful...

See the following URL for more details on Triangular joins...

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adrienne250 Right there with Babe Points: 737 More actions · Answer 3

Thanks for the info Jeff. Do you have a better way to do it?

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 4

I do. In fact, I wrote an article about it that should be coming out soon! It was submitted way back on 11 Dec 2007...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)