Search code examples
sqlsql-serverranking

Rank & find difference of value in the same column

I have the below table -

enter image description here

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 -

enter image description here

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.

Solution

  • 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.