UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Datediff Query On Timestamps From Consecutive Rows, Access 2013    
 
   
KDavidP1987
post May 23 2019, 02:10 PM
Post#1



Posts: 86
Joined: 12-June 18



Hi All,

Abbreviated Version:
I'm having some trouble completing a query to successfully perform a DateDiff against timestamps from different rows.

BackStory
I'm trying to run a query against a ticket field history table within our ticketing system database, through Access. My intent is to mimic Aging by Status calculations in Access, in short, which the front end application performs in reporting, for other uses. The table which tracks status changes, titled: dbo_MASTER3_FIELDHISTORY, has only 1 timestamp per row when a ticket's status changes. It does not inherently include a datefrom --> DateTo field to perform a datediff off of.

The following are fields in the FIELDHISTORY table (descriptions in the parenthesis): mrID (Ticket Number), mrSEQUENCE (Change Sequencing), mrFIELDNAME (mrSTATUS [status changes] or solutionEditUser), mrNEWFIELDVALUE (status ticket is changed to), mrOLDFIELDVALUE (prior Status), mrTIMESTAMP (date/time value), mrUSERID (Employee ID for who made the change)

I need to find a way to perform datediffs down to the second between status changes. I intend to use these to add up the total time and identify ticket age, with the option to exclude time in particular statuses. It is important to note that some status assignments may not have an end date, such as those that are OPEN and pending additional work, and those that have been closed (the closed timestamp would be the final one, naturally).

Attempted Solution:

In my search for a way to accomplish this I came across the following post on StackOverflow, which seemed to point the way to a solution. Unfortunately, after editing it several times to accommodate the fields in my table I could not get it to work (freezes Access when ran). I thought initially this may be caused by the mass of records, which span back to 2013... but even when including a WHERE clause that restricts tickets to those exceeding 1/1/2018 it still freezes.

Stack OverFlow Post: Date-difference-between-consecutive-rows


Original Revised Code: (From Link above, to match table fields)

CODE
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


Revised Code including TimeStamp Filters
CODE
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;


My goal is to have the query results show all current fields, plus a field for the next timestamp value, and a datediff field counting the seconds between state changes. Then this can be Summed in a later query to identify age of the ticket from creation to closure.

If anyone can offer some advice on how this can be completed, I would be most appreciative!

Thank you, in advance

Sincerely,
Kristopher


Attached File(s)
Attached File  Ex3.PNG ( 9.02K )Number of downloads: 2
Attached File  Ex2.PNG ( 5.59K )Number of downloads: 1
Attached File  Ex1.PNG ( 5.37K )Number of downloads: 2
 
Go to the top of the page
 
KDavidP1987
post May 24 2019, 09:06 AM
Post#2



Posts: 86
Joined: 12-June 18



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.


CODE
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
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 08:48 AM