指定登录时间和注销时间之间的脱机时间

Question

我一直在一个项目中工作以创建时间表遵守，我已经订购了实时时间和计划时间，以获得这两者之间的百分比。但我在实时发现了一个障碍。有些员工通过手机离线午餐，而不是设置辅助状态。下面是一个示例：

CCMS ID | Exception | LoginTime | LogoutTime | LogoutDate
--------+-----------+-----------+------------+-----------
2747738 | Open      | 09:31:00  | 12:25:00   | 2018-06-19
2747738 | AUX 1     | 12:25:00  | 12:35:00   | 2018-06-19
2747738 | Open      | 12:35:00  | 14:00:00   | 2018-06-19
2747738 | Open      | 15:00:00  | 16:35:00   | 2018-06-19
2747738 | AUX 8     | 16:35:00  | 16:38:00   | 2018-06-19
2747738 | Open      | 16:38:00  | 17:30:00   | 2018-06-19
2747738 | AUX 1     | 17:30:00  | 17:40:00   | 2018-06-19
2747738 | Open      | 17:40:00  | 18:33:00   | 2018-06-19

从上面可以看到，时间下落不明。 请注意，没有条目可以说明 14：00 到 15：00 之间的时间。 我试图完成的是解释所有时间，如下所示。在这里，您可以看到从 14：00 到 15：00 有一个新的"开放"条目，以便考虑一天中的所有时间：

CCMS ID | Exception | LoginTime | LogoutTime | LogoutDate
--------+-----------+-----------+------------+-----------
2747738 | Open      | 09:31:00  | 12:25:00   | 2018-06-19
2747738 | AUX 1     | 12:25:00  | 12:35:00   | 2018-06-19
2747738 | Open      | 12:35:00  | 14:00:00   | 2018-06-19
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
>>2747738 | Open      | 14:00:00  | 15:00:00   | 2018-06-19<< Created entry here
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2747738 | Open      | 15:00:00  | 16:35:00   | 2018-06-19
2747738 | AUX 8     | 16:35:00  | 16:38:00   | 2018-06-19
2747738 | Open      | 16:38:00  | 17:30:00   | 2018-06-19
2747738 | AUX 1     | 17:30:00  | 17:40:00   | 2018-06-19
2747738 | Open      | 17:40:00  | 18:33:00   | 2018-06-19

我希望以您的知识实现这一目标。 提前谢谢你。

Answer 1

如果你使用MSSQL，这里是解决方案(我把你的表命名为T4(

SELECT
RES.[CCMS ID]
, CASE WHEN U.[NR]=1 THEN '**hole**' ELSE RES.[Exception] END AS [Exception]
, CASE WHEN U.[NR]=1 THEN  RES.[LogoutTime] ELSE RES.[LoginTime] END AS [LoginTime]
, CASE WHEN U.[NR]=1 THEN  RES.[B LoginTime] ELSE RES.[LogoutTime] END AS [LogoutTime] 
, RES.[LogoutDate] 
FROM
(
SELECT 1
UNION
SELECT 2
) U(NR)
INNER JOIN 
(
SELECT
A.[CCMS ID], A.[Exception], A.[LoginTime], A.[LogoutTime], A.[LogoutDate] 
,B.[CCMS ID] AS [B CCMS ID], B.[Exception] AS [B Exception], B.[LoginTime] AS [B LoginTime], B.[LogoutTime] AS [B LogoutTime], B.[LogoutDate] AS [B LogoutDate] 
,CASE WHEN A.[LogoutTime]<>B.[LoginTime] THEN 1 ELSE 2 END AS [hole]
FROM
T4 A
LEFT JOIN T4 B ON 
B.[CCMS ID]=A.[CCMS ID] 
AND B.[LogoutDate]=A.[LogoutDate] 
AND B.[LoginTime]>A.[LoginTime] 
AND NOT EXISTS(
SELECT * FROM T4 C WHERE 
C.[CCMS ID]=B.[CCMS ID] 
AND C.[LogoutDate]=B.[LogoutDate] 
AND C.[LoginTime]<B.[LoginTime]
AND C.[LoginTime]>A.[LoginTime]
)
) RES ON 
CASE WHEN RES.[hole]=1 THEN 1 ELSE 0 END=U.[NR]
OR
CASE WHEN RES.[hole] IN (1,2) THEN 2 ELSE 0 END=U.[NR]

CCMS ID Exception   LoginTime   LogoutTime  LogoutDate
2747738 Open    09:31:00.0000000    12:25:00.0000000    2018-06-19
2747738 AUX 1   12:25:00.0000000    12:35:00.0000000    2018-06-19
2747738 **hole**    14:00:00.0000000    15:00:00.0000000    2018-06-19
2747738 Open    12:35:00.0000000    14:00:00.0000000    2018-06-19
2747738 Open    15:00:00.0000000    16:35:00.0000000    2018-06-19
2747738 AUX 8   16:35:00.0000000    16:38:00.0000000    2018-06-19
2747738 Open    16:38:00.0000000    17:30:00.0000000    2018-06-19
2747738 AUX 1   17:30:00.0000000    17:40:00.0000000    2018-06-19
2747738 Open    17:40:00.0000000    18:33:00.0000000    2018-06-19

http://sqlfiddle.com/#!18/a0c23/24/0