Rank & find difference of value in the same column

Question

I have the below table -

Here, I have created the "Order" column by using the rank function partitioned by case_identifier ordered by audit_date.

Now, I want to create a new column as below -

The logic for the new column would be -

select *,
case when [order] = '1' then [days_diff]
     else (val of [days_diff] in rank 2) - (val of [days_diff] in rank 1) ...
end as '[New_Col]'
from TABLE

Can you please help me with the syntax? Thanks.

Possible duplicate of [How can I subtract a previous row in sql?](http://stackoverflow.com/questions/17560829/how-can-i-subtract-a-previous-row-in-sql) — techspider, Aug 05 '16 at 20:05

Lewis Worley · Answer 1 · 2016-08-05T21:34:09.953

Take a look at the LAG function. It will provide you with what you want.

something like:

declare @temptable TABLE (case_id varchar(2), row_order int, days_diff float)
INSERT INTO @temptable values ('A',1,5)
INSERT INTO @temptable values ('A',2,3)
INSERT INTO @temptable values ('A',3,2)
INSERT INTO @temptable values ('B',1,5)
INSERT INTO @temptable values ('B',2,1)

--select * from @temptable

SELECT case_id,row_order, LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) AS prev_row,days_diff,
CASE 
WHEN row_order = 1 THEN days_diff
ELSE LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) - days_diff
END AS newcolumn
FROM @temptable
order by case_id,row_order asc


SELECT case_id,row_order,LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) AS prev_row, days_diff,
COALESCE(LAG(days_diff,1) OVER (PARTITION BY case_id ORDER BY row_order) - days_diff , days_diff)
FROM @temptable
order by case_id,row_order asc

Other answers will use a coalesce in place of the CASE statement. It's probably faster, but I feel like this is clearer.

If you run both and look at the execution plans they are the same.

score 2 · Answer 2 · answered Aug 05 '16 at 21:02

I believe the following query gets you what you want.

SELECT a.*,
 'NEW DAYS DIFF' =
CASE 
    WHEN  a.[order] = 1 THEN a.days_diff 
    ELSE a.days_diff - b.days_diff  
END 
FROM dbo.tblCaseDaysDiff a
INNER JOIN dbo.tblCaseDaysDiff b
ON
 (b.CASE_ID = a.CASE_ID AND b.[order] + 1 = a.[order] )  -- Get the current row and compare with the next highest order
OR (b.CASE_ID = a.CASE_ID AND b.[order] = 1 AND a.[order] = 1) --WHEN ORDER = 1 Get days_diff value
ORDER BY a.CASE_ID, a.[order]

score 2 · Answer 3 · answered Aug 05 '16 at 22:10

As it happens, you're already hip-deep in window functions, and as others have pointed out, LAG will do the trick. In general, though, you can always get the difference of two rows by making one row: by joining the table to itself.

with T (CASE_IDENTIFIER, AUDIT_DATE, order, days_diff)
as (
   ... your query ...
)
select a.*,
       a.days_diff - coalesce(b.days_diff, 0) as delta_days_diff
from T as a left join T as b
on a.CASE_IDENTIFIER = b.CASE_IDENTIFIER
and b.days_diff = a.days_diff - 1

score 1 · Accepted Answer · answered Aug 05 '16 at 22:44

LAG METHOD

SELECT
    CASE_IDENTIFIER
    ,AUDIT_DATE
    ,[order]
    ,days_diff
    ,days_diff - ISNULL(LAG(days_diff,1) OVER (PARTITION BY CASE_IDENTIFIER ORDER BY [order]),0) AS New_Column
FROM @Table

SELF JOIN METHOD

SELECT
    t1.CASE_IDENTIFIER
    ,AUDIT_DATE
    ,t1.[order]
    ,t1.days_diff
    ,t1.days_diff - ISNULL(t2.days_diff,0) AS New_Column
FROM
    @Table t1
    LEFT JOIN @Table t2
    ON t1.CASE_IDENTIFIER = t2.CASE_IDENTIFIER
    AND t1.[order] - 1 = t2.[order]

I feel like a lot of the other answers are on the right track but there are some nuances or easier ways of writing some of them. Or also some of the answer provide the write direction but had something wrong with their join or syntax. Anyways, you don't need the CASE STATEMENT whether you use the LAG of SELF JOIN Method. Next COALESCE() is great but you are only comparing 2 values so ISNULL() works fine too for sql-server but either will do.

Rank & find difference of value in the same column

4 Answers4