Avoiding cursor in a cumulative sum

  • 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


    110195110

    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?

  • 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.

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

  • 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

  • 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