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 -
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]'
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.