An SQL chalenge

[former_member202733]

Hi guys, how are you today?

After burn a few neurons with part of my requirement, I'm forced to ask for your help 😄

I would like to solve this with pure SQL sentences in an AMDP method, I could solve this easily in ABAP, even in an AMDP method using loops, but as I said, I would like to solve this with SQL sentences, without use any kind of loops.

Please take a look on this image:

I have 2 columns, the first I'll name as D and the second as E

The D column, is a result of a SELECT SUM, but the E colunm, is a calculated column, and it shoud work as follow:

1. Firsrt line are equals in both columns. E1 = D1
2. In the second line, the calculation for the second column is E1 + D2
3. In the third line, the calculation for the second column is E2 + D3
4. In the forth line, the calculation for the second column is E3 + D4

And so on.

So tha's it. Is it posible solve this with pure SQL sentences?

Could someone please give me a help?

Best regards.

Ronaldo S. Vieira

[Sandra_Rossi]

Also asked here with currently one answer proposed.

[former_member202733]

sandra.rossi begging for your pardon. I ask in both forum (and ask here first) because stackoverflow is not only a SAP forum and other people that non SAP people could help me. The answer provided on the messge in stackoverflow, I've try it but it does not work.

By the way I'm a big fan of your posts.

Best regards

Ronaldo S. Vieira

[ThorstenHoefer]

Hi ronaldo.silveiravieria,

Open SQL offers windows functions now.

https://help.sap.com/doc/abapdocu_755_index_htm/7.55/en-US/abensql_win_func.htm

• The window functions LEAD or LAG are suitable for calculations, such as determining the difference between values in the current row and values of the preceding or following rows.

It could also works like this:

https://stackoverflow.com/questions/30861919/what-is-rows-unbounded-preceding-used-for-in-teradata/3...

WITH data (t, a) AS (
  VALUES(1, 1),
        (2, 5),
        (3, 3),
        (4, 5),
        (5, 4),
        (6, 11)
)
SELECT t, a, sum(a) OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM data
ORDER BY t

[Sandra_Rossi]

No problem. I'm just mentioning it for people who answer here so that they can know if the issue was solved and how. Thanks for the feedback.