1

I have a field A and would want to calculate field B as follows:

B is a running total of A. Every time the running total goes over 900, B should restart its running total from 0 in the next row, as shown:

A       B
37      37
427     464
17101   17565
30      30      //Restart here because the previous value of B was >900
70      100
788     888
30      918
30      30
30      60
30      90
1120    1210
30      30
30      60
30      90
30      120

How can I get to derive B in Sybase?

Jeff
  • 12,555
  • 5
  • 33
  • 60
SouravD
  • 127
  • 1
  • 12
  • (1) What RDBMS? (2) please format this question better. You can mark tabular data as code to preserve spacing and line breaks. – Martin Smith Apr 25 '16 at 23:43
  • 1
    This does not appear to have remotely close to enough information to answer the question. "Every time the cumulative score goes above 900" implies some ordering to the records. "I keep the first instance" of what, and keep it where? – Scott Hunter Apr 25 '16 at 23:44
  • @ScottHunter What I meant was the 3rd row exceeds the sum of 900 and I keep it, but in the 4th row I pick the value from A without doing any mathematical operation. – SouravD Apr 25 '16 at 23:49
  • @MartinSmith Sybase. I don't find a format to convert my data into a table unfortunately. – SouravD Apr 25 '16 at 23:50
  • 1
    Hi @user1950075, I've edited your question to hopefully clarify your intent some. Feel free to revert the edit if I've misunderstood. What could really help here is if you show us some actual code/schema of the tables that you're referring to, as it's hard to write a query that does what you need without the actual tables/columns. – Jeff Apr 26 '16 at 04:38
  • 1
    You can do this with a couple of subselects and windowing queries: `select * from (select *, lag(MultiplesOfNineHundred) LastMultiple from (select *, sum(A) over (order by Foo.Order rows between unbounded preceding and current row) / 900 as MultiplesOfNineHundred from Foo) a) b where LastMultiple = MultiplesOfNineHundred` I think - I may have gotten myself mixed up there as I'm editing it in the comments... – Jeff Apr 26 '16 at 04:56
  • @Jeff. Thanks for the edit. This is what I meant. – SouravD Apr 27 '16 at 09:32
  • If Sybase handles recursive CTE then you could use it [demo](https://stackoverflow.com/a/52936314) – Lukasz Szozda Jan 02 '19 at 15:34

1 Answers1

1

Oracle's MODEL function is what you're probably looking for. Number 7 on the following website will help you if you're using Oracle.

houstonwp
  • 81
  • 1
  • 7
  • This is what I want exactly. Although I am using sybase, I shall try and find a model equivalent. Really helpful. – SouravD Apr 25 '16 at 23:59