CODEWITHSUNDEEP
sqlsql-server
George
George

Reputation: 347

Subtract Date field from previous row, reset when value in other field changes

I am trying to write a SQL script that accomplishes the following:

Creates a column which subtracts the value in the Date Field from the value in the Date field from the previous row. This should reset and start over when the ID field changes.

The OpID and Resolutiondate field are fixed, and I am trying to create a column like the one see below.

enter image description here

Upvotes: 0

Views: 340

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use lag(). Date/time functions are notoriously database-specific, but the idea is:

select t.*,
       (datefield - lag(datefield) over (partition by id order by datefield)) as diff
from t;

Upvotes: 1

Related Questions