Link to home
Create AccountLog in
Avatar of vtechdev
vtechdevFlag for United States of America

asked on

SQL How To DateDiff between a record and the next record

I need the best way to present data for reporting when the raw data is a log of status changes that show the jobID, status, and date the job was set to the status shown in the record. The reports need to show how long the job remained at each status, but the log only contains the date the status was set, so the duration requires that we calculate the date diff from one record with a given jobid to the next (sequentially in time) record for the same jobid (regardless of the status code for the next record).
I realize we can sort by jobid and date, but what I'm not certain about is the best method to create a view that calculates the date diff between 2 records sequential records, or how to handle the last record when no additonal record exists for the jobid. I know I could write a user defined function to lookup the next record, and calculate the datediff but I don't know if there is a better method. Looking for ideas.
Avatar of chapmandew
chapmandew
Flag of United States of America image

can you post your table structure and some example data?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer