I have a table with policy no,status code,status date.
I want to get the difference of the status dates for each policy(will have more than one record with different status codes)after getting sorted with the status dates for each policy.
The difference of the status dates should only consider certain status codes.
for eg: consider that 5,6 are the status codes to be considered
|pol 1||5||1st june|
|pol 1||6||2nd june|
|pol 1||7||4th june|
|pol 1||6||6th june|
|pol 1||8||9th june|
|pol 1||9||10th june|
|pol 1||10||11th june|
i should get the op as
which is difference of (4th june-1st june)+(9th june-6th june)
you can use
MAX(status_date) OVER (PARTITION BY policy_no ORDER BY status_date ROWS BETWEEN PRECEDING 1 and PRECEDING 1)
or something similar to get the "previous" date for the dates you mention.