Derivative / time difference for time series data in SQL

user_11585543 0
Viewed 254

I am querying a dataset of requests and am looking to get "up-time" between service requests. This post Discrete Derivative in SQL is pretty much what I am after. However, I am working with date time data and I don't believe I can assume the work orders will be sorted chronologically. Also, I have specific locations to each request.

Request Location RequestDate CompleteDate
R-001 a 1/1/2020 1/5/2020
R-002 a 3/17/2020 3/20/2020
R-004 b 2/27/2020 3/1/2020
R-003 b 4/22/2020 4/23/2020
R-005 a 7/3/2020 7/7/2020

I do see the SQL DATEDIFF() function. Below is a slight modification from the top answer in the referenced post.

Select a.RequestDate as StartTime
     , b.CompleteDate as EndTime
     , DATEDIFF(b.RequestDate-a.CompleteDate) as TimeChange
FROM YourTable a 
Left outer Join YourTable b ON b.time>a.time
Left outer Join YourTable c ON c.time<b.time AND c.time > a.time
Where c.time is null
Order By a.RequestDate

Also, I kind of grasp aliasing the same table twice but not fully. I don't understand the outer join either. Eventually I'd like the new column...I might be able to make the assumption or order by request date earlier if that simplifies the problem.

Request Location RequestDate CompleteDate Up-Time
R-001 a 1/1/2020 1/5/2020 null
R-002 a 3/17/2020 3/20/2020 72
R-004 b 2/27/2020 3/1/2020 null
R-003 b 4/22/2020 4/23/2020 52
R-005 a 7/3/2020 7/7/2020 105

I am hoping it isn't too different from the original post, with a group by location and order by request date thrown in there somewhere.

postgresql sql
1 Answers

This uses the LAG window function to perform a difference (in days) between this RequestDate and the previous CompleteDate for each Location separately, ordered by the RequestDate (some assumptions here).

CREATE TABLE log (
    Request      VARCHAR(30)
  , Location     VARCHAR(10)
  , RequestDate  date
  , CompleteDate date
);

INSERT INTO log VALUES 
  ('R-001', 'a',  '2020-1-1',    '2020-1-5' )
, ('R-002', 'a',  '2020-3-17',   '2020-3-20')
, ('R-004', 'b',  '2020-2-27',   '2020-3-1' )
, ('R-003', 'b',  '2020-4-22',   '2020-4-23')
, ('R-005', 'a',  '2020-7-3',    '2020-7-7' )
;

The query:

SELECT log.*
     , LAG(CompleteDate) OVER (PARTITION BY Location ORDER BY RequestDate) AS prev_date
     , RequestDate - LAG(CompleteDate) OVER (PARTITION BY Location ORDER BY RequestDate) AS diff
  FROM log
 ORDER BY Location, RequestDate 
;

Result:

Result of Test

Working test case

Additional SQL to address the string to date conversion issue (PostgreSQL):

WITH logs AS (
        SELECT Request, Location
             , TO_DATE(RequestDate, 'MM/DD/YYYY')  AS RequestDate
             , TO_DATE(CompleteDate, 'MM/DD/YYYY') AS CompleteDate
          FROM log
     )
SELECT log.*
     , LAG(CompleteDate) OVER (PARTITION BY Location ORDER BY RequestDate) AS prev_date
     , RequestDate - LAG(CompleteDate) OVER (PARTITION BY Location ORDER BY RequestDate) AS diff
  FROM logs AS log
 ORDER BY Location, RequestDate 
;

The updated working test case

user_24456290
Related