0

I have a SQL Server table that contains some data and I need to do some complex equations to get some result in same table

The table structure and data like the following:

A   B                  C           D        E
-------------------------------------------------
5   2015 / 2016        0           0        0
7   2017 / 2018        0           24000    0
8   2018 / 2019        0           24000    0
9   2019 / 2020        0           26000    0
10  2020 / 2021        36000       0        0

I need to update

E9 = IF C10 > D9 = D9
     IF C10 < D9 = C10
     IF C10 = D9 = C10 or D9

E8 = IF C10 - E9 > D8 = (C10-E9) - D8
     IF C10 - E9 < D8 = C10-E9
     IF C10 - E9 = D8 = C10-E9 or D8

And so on till to reach to first row.

The result should be

A   B                  C           D        E
-------------------------------------------------
5   2015 / 2016        0           0        0
7   2017 / 2018        0           24000    0
8   2018 / 2019        0           24000    10000
9   2019 / 2020        0           26000    26000
10  2020 / 2021        36000       0        0

Column E Sum should equal C10.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amr Elnashar
  • 1,739
  • 12
  • 33
  • 53
  • If you are looking to do this retrospectively, you could use an UPDATE statement with CASE (https://www.w3schools.com/sql/sql_case.asp) Something like UPDATE dbo.table SET C = CASE WHEN (C > D) THEN D END – Paul James Jan 10 '21 at 19:09
  • @PaulJames, But update will reflect row by row but I need to compare row with his previous row. – Amr Elnashar Jan 10 '21 at 19:13
  • 1
    I'm not sure if I follow your questions right, but I think the best way to get about this equation is by creating a recursive CTE that starts off on your last row and then works it's way backwards until the very end. So you're going to have an UDPATE statement with the CASE statement that @PaulJames said, but using a recursive CTE. – Isaac-Mick Jan 10 '21 at 20:50
  • @Isaac-Mick, could u please provide an example. – Amr Elnashar Jan 10 '21 at 21:53
  • I heard about LEAD and LAG functions are these helpful for this scenario? – Amr Elnashar Jan 10 '21 at 22:36
  • @AmrElnashar While I am trying to get the query together, did you mean to leave out id = 6 and year 2016 / 2017 or was that an accident? – Isaac-Mick Jan 11 '21 at 22:18
  • Also, could you provide what E7 = IF statement. I am still a bit confused on the algorithm we're going for is. I am confused on the role of C10 in all of this. Would E7 = IF (C10 - E9 - E8) > D7 = (C10 - E9 - E8) - D7? – Isaac-Mick Jan 11 '21 at 23:07

0 Answers0