CODEWITHSUNDEEP
sqlsql-serverlag
Anna
Anna

Reputation: 974

Calculate difference between rows and keep the first row always 0?

I have to calculate the difference between row values in Table X (SQL Server)

Table X

ID  A 
1   100
2   200
3   300
4   400

So I wrote the following SQL query

SELECT ID,
       A
       A - COALESCE (lag(A) OVER (ORDER BY date), 0) AS Difference
FROM Table X

And the result is

ID  A    Difference
1   100   100
2   200  -100
3   300  -100
4   400  -100

What I want is to keep the first-row Difference always as 0

ID  A    Difference
1   100   0
2   200  -100
3   300  -100
4   400  -100

But I have no idea how to do it.

Upvotes: 0

Views: 900

Answers (3)

Zhorov
Zhorov

Reputation: 29943

You may try to pass а value for the default parameter of the LAG() window function. As is explained in the documentation, the default parameter is the value to return when offset is beyond the scope of the partition (and for the first row, the previous row is beyond that scope).

Table:

CREATE TABLE Data (ID int, A int, [Date] date)
INSERT INTO Data (ID, A, [Date]) 
VALUES 
  (1, 100, '20200701'), 
  (2, 200, '20200702'), 
  (3, 300, '20200703'), 
  (4, 400, '20200704')

Statment:

SELECT 
   ID,
   A,
   LAG(A, 1, A) OVER (ORDER BY [Date]) - A AS Difference
FROM Data

Result:

ID  A   Difference
------------------
1   100 0
2   200 -100
3   300 -100
4   400 -100

Upvotes: 2

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the following query.

For this type of query order by clause is important based on the column and the applied order by clause ascending or descending the result can be different.

create table Test(ID int,
A int)

insert into Test values
(1,   100),
(2,   200),
(3,   300),
(4,   400)

SELECT ID
,A
,Difference
FROM (
SELECT ID
    ,A
    ,isnull(A - LAG(A) OVER (
            ORDER BY A DESC
            ), 0) Difference
FROM test
) t

Live Demo

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 12959

Thanks to @zhorov for the table schema, data

You can use ISNULL or COALESCE to arrive at the difference.

DECLARE @Data table(ID int, A int, [Date] date)
INSERT INTO @Data (ID, A, [Date]) 
VALUES 
  (1, 100, '20200701'), 
  (2, 200, '20200702'), 
  (3, 300, '20200703'), 
  (4, 400, '20200704')

  SELECT ID,A, ISNULL(LAG(A,1) OVER(ORDER BY DATE),A) AS difference FROM @Data
--or you can use COALESCE
  SELECT ID,A, COALESCE(LAG(A,1) OVER(ORDER BY DATE),A) AS difference FROM @Data


+----+-----+------------+
| ID |  A  | difference |
+----+-----+------------+
|  1 | 100 |        100 |
|  2 | 200 |        100 |
|  3 | 300 |        200 |
|  4 | 400 |        300 |
+----+-----+------------+

Upvotes: 1

Related Questions