每 10 秒分组一次的生产计数

奥利弗舍姆

我想显示每 10 秒分组的最后一小时的生产计数。
到目前为止我已经这样做了

SELECT 

COUNT(*) AS [PRODUCTION],
(DATEPART(HOUR, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))) AS [HOUR],
(DATEPART(MINUTE, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))) AS [MIN],
(DATEPART(SECOND, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))) AS [FROMSECOND]

FROM tblTransaction
WHERE TL_Timestamp BETWEEN  DATEADD(HOUR,-1, GETDATE())  AND GETDATE()
AND TL_IsAccepted = 1
GROUP BY 
(DATEPART(HOUR, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))),
(DATEPART(MINUTE, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))),
(DATEPART(SECOND, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp)))
ORDER BY 
(DATEPART(HOUR, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))) DESC,
(DATEPART(MINUTE, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))) DESC,
(DATEPART(SECOND, DATEADD(SECOND, -(DATEPART(SECOND, TL_Timestamp)) % 10, TL_Timestamp))) DESC

这工作正常，但如果生产没有发生整整 10 秒，则不考虑在内。我希望它为零。
有任何想法吗？

埃萨

您可以创建一个包含所有时间戳组（预填充和固定）的表，并将其作为连接中的第一个表（另一个在左连接中）。

例如。（根据您的情况，它们应该是360条记录，即一个小时，或者一整天是8640条）

ID   MIN SEC  
1    00 10  
2    00 20  
3    00 30

...如果你叫它 TL_TIME_DIVISION

SELECT TL_TIME_DIVISION.HOUR, TL_TIME_DIVISION.MIN, TL_TIME_DIVISION.SEC, COALESCE (PRODUCTION,0)  AS PRODUCTION
FROM TL_TIME_DIVISION   
LEFT JOIN **your query** ON **use Hour, min e sec**

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。