September 29, 2019 at 4:35 am
Can you help?
I have a table like below.
ID SrNum Status Time
1 1 Begin 9/29/2019 9:00:05.656 AM
1 2 Request Info 9/29/2019 15:00:04.234 PM
1 3 Received 10/1/2019 8:50:45.012 AM
1 4 Reviewed 10/5/2019 4:00:23.123 PM
1 5 Completed 10/5/2019 4:03:50.468 PM
2 1 Begin 9/29/2019 10:10:15.354 AM
2 2 Received 10/1/2019 3:45:20.123 PM
2 3 Completed 10/5/2019 10:06:05.354 AM
I need to write a query that shows time difference between Received and Completed for each ID. I also need the time difference between Received and the next status.
Note that the SrNum shown above are sorted in the ascending order. They need not appear already sorted in the database.
Output should have ID number and the time difference between the above mentioned statuses.
Please help.
September 29, 2019 at 1:59 pm
;WITH Data AS
(
SELECT * FROM (VALUES
(1, 1, 'Begin', CONVERT(datetime2,'9/29/2019 9:00:05.656 AM')),
(1, 2, 'Request Info', '9/29/2019 15:00:04.234 PM'),
(1, 3, 'Received', '10/1/2019 8:50:45.012 AM'),
(1, 4, 'Reviewed', '10/5/2019 4:00:23.123 PM'),
(1, 5, 'Completed', '10/5/2019 4:03:50.468 PM'),
(2, 1, 'Begin', '9/29/2019 10:10:15.354 AM'),
(2, 2, 'Received', '10/1/2019 3:45:20.123 PM'),
(2, 3, 'Completed', '10/5/2019 10:06:05.354 AM')) T(ID, SrNum, Status, Time)
)
SELECT r.ID,
r.Time ReceivedTime,
c.Time CompletedTime,
n.Time NextFromReceivedTime,
DATEDIFF(mi, r.Time, c.Time) RecievedToCompleted_Minutes,
DATEDIFF(mi, r.Time, n.Time) RecievedToNextStep_Minutes
FROM [Data] r
LEFT JOIN [Data] c
ON c.ID = r.ID
AND c.Status='Completed'
LEFT JOIN [Data] n
ON n.ID = r.ID
AND n.SrNum = r.SrNum + 1
September 30, 2019 at 2:55 am
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply