-5

or

As you can see, the Menge column is decreasing its value for every row, subtracting the Reserviert value from the previous row.

Can anybody suggest how to write this type of query in SQL Server?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
  • 3
    Most people here want sample table data _and the expected result_ as formatted text, not as images. – jarlh Nov 19 '19 at 13:54
  • 1
    You need to have a **proper** ordering criteria, so that you can establish the sequence of the rows - and then, depending on which **version** of SQL Server you're using, you might be able to use `LAG` command to handle this "new row's `Menge` is previous row's `Menge` minus the previous row's `Reserviert` value – marc_s Nov 19 '19 at 13:59
  • i have used LAG() but its not happening. Actually it is the same field which is updating every time –  Nov 19 '19 at 14:16
  • Could you give some additional information? – Vyron Paschalidis Nov 19 '19 at 14:36
  • @VyronPaschalidis What Kind of information Sir ?? –  Nov 19 '19 at 14:37
  • Actually i want only the query how can write a query which gives me ("Menge"-"Reserviet ") result and it will be updated in the "Menge" automatically in the query itself –  Nov 19 '19 at 14:38
  • @SiddharthaDas Gordon Linoff has already given the answer but you need to tinker with it to have it exactly as you need. We won't write the query for you, you have the information needed. – Vyron Paschalidis Nov 19 '19 at 14:54

2 Answers2

2

You can use window functions -- in particular a cumulative sum. It is not clear where the 200 is coming from, so I will hardcode it:

select t.*,
       (200 -
        sum(reservevert) over (partition by artikelnumber order by coalesce(lieferdatum, '2000-01-01')
       ) as merge          
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • As you can see in the image it's just the same field updating every time. –  Nov 19 '19 at 14:15
1

It Cannot be Done by SQL Only . It must to be done by Code