Calculate time different from previous record

Question

I have a set of data that I want to determine the difference in days between the Begin_time and End_Time for every 2 records to determine the processing time. I'm familiar with DateDiff('d','End_Time','Begin_Time',) to determine the processing time on the same row but how do I determine this for the previous record? For example, something like this DateDiff('Record2.Begin_time','Record1.End_Time') then DateDiff('Record4.Begin_time','Record3.End_Time') then DateDiff('Record6.Begin_time','Record5.End_Time') etc. It doesn't have to use DateDiff function, I'm just using that to illustrate my question. thanks

> Record    Begin_Time  End_Time    Processing_Time
  1         11/23/2020  11/24/2020  1
  2         11/23/2020  11/24/2020  1
  3         11/30/2020  11/30/2020  0
  4         11/30/2020  11/30/2020  0
  5         11/2/2020   11/3/2020   1
  6         11/2/2020   11/3/2020   1
  7         11/3/2020   11/5/2020   2
  8         11/3/2020   11/5/2020   2

I believe there is a similar question with answers and examples [here](https://stackoverflow.com/q/39529045/14956277). — D M, Jan 11 '21 at 21:25
Window function `LAG(Date) OVER (...` there are a million examples — Charlieface, Jan 11 '21 at 22:15

score 0 · Answer 1 · answered Jan 12 '21 at 00:22

0

An Aproach could be like this:

Select DateDiff(YourTableEven.Begin_time, YourTableOdd.End_Time)
From YourTable AS YourTableEven
Join  YourTable AS YourTableOdd ON YourTableOdd.Record = YourTableEven.Record + 1
Where YourTableEven.Record % 2 = 0

answered Jan 12 '21 at 00:22

MundoPeter

704
6
12

Calculate time different from previous record

1 Answers1