CODEWITHSUNDEEP
sqlms-accessrowsdatediffsequential
Kristopher Penland
Kristopher Penland

Reputation: 45

Access 2013 Query, DateDiff from Consecutive Rows TimeStamps

I'm facing a problem successfully completing (running) a query on a singular table in Access 2013 using SQL to complete a Datediff on consecutive/Sequential rows of timestamps, which track status changes in tickets going through our ticketing system.

The table titled: dbo_Master3_FieldHistory, has a field which tracks timestamps each time a ticket's status changes. Unfortunately, it only includes 1 timestamp per change, meaning it doesn't inherently have a secondary timestamp for when the status is changed again, which I need to run a DateDiff to calculate AGE for tickets, based on Status.

I found a plausible solution for this on StackOverflow, linked below. When i tried to implement this solution, as is with minor adjustments, and including adjustments for filtering out old data and particular fields, it just freezes my Access program and never times out (have to force close Access)

Date Difference between consecutive rows

'This is the basic code, traslated from the linked StackOverflow solution to fit this tables fields (I believed)

SELECT T.mrID, T.mrSEQUENCE, T.mrUSERID, T.mrFIELDNAME, T.mrNEWFIELDVALUE, T.mrOLDFIELDVALUE, T.mrTIMESTAMP, T.mrNextTIMESTAMP, DateDiff("s",T.mrTIMESTAMP, T.mrNextTIMESTAMP) AS STATUSTIME
FROM ( 

SELECT T1.mrID, T1.mrSEQUENCE, T1.mrUSERID, T1.mrFIELDNAME, T1.mrNEWFIELDVALUE, T1.mrOLDFIELDVALUE, T1.mrTIMESTAMP, 

(SELECT MIN(mrTIMESTAMP)
FROM dbo_MASTER3_FIELDHISTORY AS T2
WHERE T2.mrID = T1.mrID
AND T2.mrTIMESTAMP > T1.mrTIMESTAMP
) As mrNextTIMESTAMP

FROM dbo_MASTER3_FIELDHISTORY AS T1

) AS T

'This is the code that I wanted to use to account for filtering out two particular fields, limiting the data to tickets (mrID) newer than 1/1/2018 and only those where the mrFIELDNAME is mrSTATUS

SELECT T.mrID, T.mrSEQUENCE, T.mrUSERID, T.mrFIELDNAME, T.mrNEWFIELDVALUE, T.mrOLDFIELDVALUE, T.mrTIMESTAMP, T.mrNextTIMESTAMP, DateDiff("s",T.mrTIMESTAMP, T.mrNextTIMESTAMP) AS STATUSTIME
FROM ( 

SELECT T1.mrID, T1.mrSEQUENCE, T1.mrUSERID, T1.mrFIELDNAME, T1.mrNEWFIELDVALUE, T1.mrOLDFIELDVALUE, T1.mrTIMESTAMP, 

(SELECT MIN(mrTIMESTAMP)
FROM dbo_MASTER3_FIELDHISTORY AS T2
WHERE mrFIELDNAME = "mrSTATUS"
AND T2.mrID = T1.mrID
AND T2.mrTIMESTAMP > T1.mrTIMESTAMP
) As T1.mrNextTIMESTAMP

FROM dbo_MASTER3_FIELDHISTORY AS T1
WHERE mrFIELDNAME = "mrSTATUS"
AND mrTIMESTAMP >= #1/1/2018#

) AS T;

Access freezes when I try to run these queries. I've tried several ways but can't get it to work

Upvotes: 1

Views: 32

Answers (1)

Kristopher Penland
Kristopher Penland

Reputation: 45

I was able to figure it out, thank you to those who took your time to read through this interesting challenge. Instead of using the second code set in the link provided, I utilized the first and it worked beautifully. With some additions to the code to account for other filters/criteria, I have the results I need.


SELECT T1.mrID, T1.mrSEQUENCE, T1.mrUSERID, T1.mrFIELDNAME, T1.mrNEWFIELDVALUE, T1.mrOLDFIELDVALUE, T1.mrTIMESTAMP, MIN(T2.mrTIMESTAMP) AS mrNextTIMESTAMP, DATEDIFF("s", T1.mrTIMESTAMP, MIN(T2.mrTIMESTAMP)) AS TimeInStatus
FROM ((dbo_MASTER3_FIELDHISTORY AS T1 LEFT JOIN dbo_MASTER3_FIELDHISTORY AS T2 ON (T2.mrTIMESTAMP > T1.mrTIMESTAMP) AND (T1.mrID = T2.mrID)) INNER JOIN dbo_MASTER3 AS T4 ON (T4.mrID = T1.mrID))
WHERE T4.mrSUBMITDATE >= #1/1/2018#
AND t1.mrFIELDNAME = "mrSTATUS"
AND NOT T4.mrSTATUS="_Deleted_"
AND NOT T4.mrSTATUS="_SOLVED_"
AND NOT T4.mrSTATUS="_PENDING_SOLUTION_"
GROUP BY T1.mrID, T1.mrSEQUENCE, T1.mrUSERID, T1.mrFIELDNAME, T1.mrNEWFIELDVALUE, T1.mrOLDFIELDVALUE, T1.mrTIMESTAMP
ORDER BY T1.mrID, T1.mrTIMESTAMP;

Sincerely, Kristopher

Upvotes: 1

Related Questions