August 21, 2008 at 4:08 am
Guys,
I have a table T with columns: a, b and i.
T(i int, a float, b float)
Rows in the table are ordered by i. For each row of the table I need to calculate cumulative sum f (you may consider f being another column in T). Calculations start from value c that is constant. Value of f for each row (i) is the value of f for the previous row (i-1) plus current value of a. But in each row value of b is the upper limit for f. I hope I'm making myself clear so far:)
An expression I would use to describe how f is being calculated is:
f(i) = min(fi-1 + ai; bi)
f(0) = c
i = 0,1,2,3,...
And this is an example of what I want to get:
c = 100
iabf
210195120
310195130
410195140
510195150
610195160
710195170
810195180
910195190
1010195195
1110195195
1210195195
1310218205
1410218215
1510218218
1610195195
1710195195
1810195195
I.e.:
f(i=1) = min(100 + 10; 195) = 110
f(i=10) = min(190 + 10; 195) = 195
I've spend a day on it and it seems I have no other choice but to use a cursor.:unsure: Or is there a smart SQL statement out there that I could use?
August 21, 2008 at 7:44 am
This link is to an article about accessing the Previous Row.
It should help you get started. If you search this SQLServerCentral for "Previous Row" you will find a number of other articles that cover various problems like this one.
Good Luck.
August 21, 2008 at 11:33 am
Here's a good article on running totals without cursors.
You could easily add into the case statement something about the total being greater than b.
Edit: http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2008 at 12:05 pm
Lost your link, Gus.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply