时间戳

首页 > 解决方案 > 访问 2013 查询，来自连续行时间戳的 DateDiff

sql - 访问 2013 查询，来自连续行时间戳的 DateDiff

问题描述

我遇到了一个问题，成功地完成（运行）在 Access 2013 中的单个表上的查询，使用 SQL 在时间戳的连续/顺序行上完成 Datediff，该时间戳跟踪通过我们的票务系统的票证的状态变化。

标题为：dbo_Master3_FieldHistory 的表有一个字段，用于跟踪每次工单状态更改时的时间戳。不幸的是，它每次更改仅包含 1 个时间戳，这意味着它本质上没有用于再次更改状态时的辅助时间戳，我需要根据 Status 运行 DateDiff 来计算票证的 AGE。

我在 StackOverflow 上找到了一个合理的解决方案，链接如下。当我尝试实施此解决方案时，与细微调整一样，包括过滤旧数据和特定字段的调整，它只会冻结我的 Access 程序并且永远不会超时（必须强制关闭 Access）

连续行之间的日期差异

'这是基本代码，从链接的 StackOverflow 解决方案转换为适合此表字段（我相信）

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

'这是我想用来过滤两个特定字段的代码，将数据限制为 2018 年 1 月 1 日之后的票证 (mrID)，并且仅限于 mrFIELDNAME 为 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;

当我尝试运行这些查询时，访问冻结。我尝试了几种方法，但无法使其正常工作

标签： sqlms-accessrowsdatediffsequential

解决方案

我能够弄清楚，感谢那些花时间阅读这个有趣挑战的人。我没有使用提供的链接中的第二个代码集，而是使用了第一个，并且效果很好。通过添加一些代码以考虑其他过滤器/标准，我得到了我需要的结果。


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;

真诚的，克里斯托弗

推荐阅读