0

I need to create a column in a view which is derived as below.

The calculated column is derived from Amount column where a row value is derived by subtracting from previous row value of Amount, and every 1st contract that starts will have no value as it's the first row of that contract.

Can someone please suggest what option I have to achieve this?

Contract DateValue Amount Calculated Column
111 20-June-2023 100
111 21-June-2023 150 50
111 21-June-2023 250 100
111 21-June-2023 200 50
222 20-June-2023 300
222 21-June-2023 350 50
222 22-June-2023 450 100
333 23-June-2023 100
333 24-June-2023 200 100
333 24-June-2023 250 50

Any suggestions are welcome.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Govind chouhan
  • 55
  • 1
  • 11
  • You can't do this as a computed column; they can only refer to columns in the same row. You would be better off using a `VIEW` if you want this readily queriable. – Thom A Jun 25 '23 at 10:02
  • I'm using a View, How do i do it in view. – Govind chouhan Jun 25 '23 at 10:03
  • 1
    Side note: the format of your dates implies you are storing them as a string based; I hope *not*. – Thom A Jun 25 '23 at 10:03
  • Have you had a look at `LAG`/`LEAD`? What have you tried? Why didn't it work? – Thom A Jun 25 '23 at 10:04
  • I have not explored LAG/LEAD, can you share some other answer that i can refer. New to SQL. – Govind chouhan Jun 25 '23 at 10:04
  • 1
    [LAG](https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16)/[LEAD](https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver16); the documentation contains plenty of examples. – Thom A Jun 25 '23 at 10:06
  • I think LAG can solve the problem of getting previous value, But how do I reset it when new Contract Starts. Any Suggestion on that. – Govind chouhan Jun 25 '23 at 10:07
  • 1
    Why is the 4th row computed value being reported as _positive_, given that it is a decrease? – Tim Biegeleisen Jun 25 '23 at 10:08
  • 1
    *"But how do I reset it when new Contract Starts."* You don't; you partition those groups. Think in "sets", tables **don't** contain a set of *ordered* rows. – Thom A Jun 25 '23 at 10:10
  • [Subtract previous row value to current row](https://stackoverflow.com/questions/54129588/subtract-previous-row-value-to-current-row) – Thom A Jun 25 '23 at 10:14
  • I'm only interested in absolute difference of 2 values, Positive and negative are irrelevant for my business purpose, Hence 4th row value is also positive. – Govind chouhan Jun 25 '23 at 10:23
  • You don't mention that in your question, @Govindchouhan , you just state *"by subtracting from previous row"*, which actually means that all but the row queried above should be negative values. – Thom A Jun 25 '23 at 10:27

2 Answers2

2

This is a way to do it using window function LAG() to get the previous value :

select *, Amount - lag(Amount) over (partition by Contract order by DateValue) as [Calculated Column]
from mytable

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29
0

Your data has no way to determine the order of duplicate DateValues so I will assume you also have an identity id column. (A relation is an unordered set!)

Why does 200 - 250 = 50?

SELECT id, [Contract], DateValue, Amount
   ,Amount -
      LAG(Amount) OVER (PARTITION BY [Contract] ORDER BY DateValue, id) AS CalcValue
FROM YourTable;
Aardvark
  • 174
  • 1
  • 7