时间戳

首页 > 解决方案 > 实时监控

sql-server-2019 - 实时监控

问题描述

我试图确定员工何时在多个主要位置同时显示 in(1) 的打卡状态。如果他们在同一个主要位置的多个子位置被打孔，那很好，因为这将是工作的正常部分。但是，应该不可能在多个主要位置打卡，这些都需要进行审查。下面我已经包含了表格的布局以及信息以及一组样本数据。在这个样本集中，我们正在研究我们可以预期的三种类型的场景。绿色部分显示同一主要位置的输入 (1) 和输出 (2)，当天没有其他主要位置，甚至没有子位置。蓝色部分显示重叠的打孔，但它们位于相同的主要位置，只是不同的子位置，这也很好。然而，橙色部分是我们想要找到的。本节显示不同主要位置的重叠打孔。我希望从输出中看到两条记录，它们显示了不同位置的 ins 信息。所以在这种情况下，只需输出前两条橙色线。我希望能够尽可能近乎实时地识别这一点，并将输出发送到电子邮件发行版以供审查。由于时间范围可能变化很大，因此仅监视最后一两个小时并不总是有足够的数据，如本例所示。我正在考虑检查当前存在于进出之间的最长时间范围，然后将其加倍并将其用于监控时间范围，但我不确定这是否是正确的方法。本节显示不同主要位置的重叠打孔。我希望从输出中看到两条记录，它们显示了不同位置的 ins 信息。所以在这种情况下，只需输出前两条橙色线。我希望能够尽可能近乎实时地识别这一点，并将输出发送到电子邮件发行版以供审查。由于时间范围可能变化很大，因此仅监视最后一两个小时并不总是有足够的数据，如本例所示。我正在考虑检查当前存在于进出之间的最长时间范围，然后将其加倍并将其用于监控时间范围，但我不确定这是否是正确的方法。本节显示不同主要位置的重叠打孔。我希望从输出中看到两条记录，它们显示了不同位置的 ins 信息。所以在这种情况下，只需输出前两条橙色线。我希望能够尽可能近乎实时地识别这一点，并将输出发送到电子邮件发行版以供审查。由于时间范围可能变化很大，因此仅监视最后一两个小时并不总是有足够的数据，如本例所示。我正在考虑检查当前存在于进出之间的最长时间范围，然后将其加倍并将其用于监控时间范围，但我不确定这是否是正确的方法。我希望从输出中看到两条记录，它们显示了不同位置的 ins 信息。所以在这种情况下，只需输出前两条橙色线。我希望能够尽可能近乎实时地识别这一点，并将输出发送到电子邮件发行版以供审查。由于时间范围可能变化很大，因此仅监视最后一两个小时并不总是有足够的数据，如本例所示。我正在考虑检查当前存在于进出之间的最长时间范围，然后将其加倍并将其用于监控时间范围，但我不确定这是否是正确的方法。我希望从输出中看到两条记录，它们显示了不同位置的 ins 信息。所以在这种情况下，只需输出前两条橙色线。我希望能够尽可能近乎实时地识别这一点，并将输出发送到电子邮件发行版以供审查。由于时间范围可能变化很大，因此仅监视最后一两个小时并不总是有足够的数据，如本例所示。我正在考虑检查当前存在于进出之间的最长时间范围，然后将其加倍并将其用于监控时间范围，但我不确定这是否是正确的方法。我希望能够尽可能近乎实时地识别这一点，并将输出发送到电子邮件发行版以供审查。由于时间范围可能变化很大，因此仅监视最后一两个小时并不总是有足够的数据，如本例所示。我正在考虑检查当前存在于进出之间的最长时间范围，然后将其加倍并将其用于监控时间范围，但我不确定这是否是正确的方法。我希望能够尽可能近乎实时地识别这一点，并将输出发送到电子邮件发行版以供审查。由于时间范围可能变化很大，因此仅监视最后一两个小时并不总是有足够的数据，如本例所示。我正在考虑检查当前存在于进出之间的最长时间范围，然后将其加倍并将其用于监控时间范围，但我不确定这是否是正确的方法。

我已经考虑过将数据输入到表/视图中，该表/视图仅保留活动的出拳，并且当有人出拳时，它只会删除输入。然后为同一个 EmpID 有多个记录时设置某种标志不同的主要位置发送通知。我只是不知道该怎么做。任何关于如何做到这一点或我可以研究可能有帮助的事情的想法将不胜感激。此数据存储在 SQL 2019 服务器中。它是我们的报告服务器，正在使用复制从生产服务器提供数据。我们可以添加任何可能有用的列/索引或其他对象。

Create Table TimeSheet(
EmpID int not null
,PrimaryLocation int not null  
,SubLocation int not null 
,PunchStatus smallint not null ----(1 in, 2 out)
,PunchTime DateTime not null
)

例子

编辑：

现在发布我自己的问题的答案还为时过早，但我认为我得到了可以工作的代码。我什至添加了一些额外的场景来测试，它适用于这些场景。我仍然需要在实际数据库上对其进行测试，该数据库每天将有数百万条记录，但以下是我使用的。它可能会大大改进，如果您对此有任何想法，请告诉我。

If OBJECT_ID('tempdb.dbo.#TimeSheet')   IS NOT NULL     DROP TABLE #TimeSheet
If OBJECT_ID('tempdb.dbo.#Test')        IS NOT NULL     DROP TABLE #Test
If OBJECT_ID('tempdb.dbo.#Window')      IS NOT NULL     DROP TABLE #Window
If OBJECT_ID('tempdb.dbo.#WindowPrep')      IS NOT NULL     DROP TABLE #WindowPrep

Create Table #TimeSheet(
 EmpID int not null
,PrimaryLocation int not null  
,SubLocation int not null 
,PunchStatus smallint not null ----(1 in, 2 out)
,PunchTime DateTime not null
)

Insert Into #TimeSheet
Values   (1,5,2,1,'5/2/21 12:56 PM')
        ,(1,5,2,2,'5/2/21 3:00 PM')
        ,(1,4,2,1,'5/2/21 3:56 PM')
        ,(1,4,2,2,'5/2/21 5:00 PM')
        ,(1,4,2,1,'5/5/21 8:24 PM')
        ,(1,4,1,1,'5/5/21 9:24 PM')
        ,(1,4,1,2,'5/5/21 10:24 PM')
        ,(1,4,2,2,'5/5/21 11:24 PM')
        ,(1,2,8,1,'5/20/21 8:44 AM')
        ,(1,2,7,1,'5/20/21 9:44 AM')
        ,(1,2,7,2,'5/20/21 3:44 PM')
        ,(1,3,2,1,'5/20/21 5:52 PM')
        ,(1,3,2,2,'5/20/21 6:30 PM')
        ,(1,2,8,2,'5/20/21 6:00 PM')


;With cte_In
as
(
select
     EmpID
    ,PrimaryLocation
    ,SubLocation
    ,PunchStatus    as PunchIn
    ,PunchTime      as InTime

From #TimeSheet as T

Where T.PunchStatus = 1
)
,cte_Out
as
(
select
     EmpID
    ,PrimaryLocation
    ,SubLocation
    ,PunchStatus    as PunchOut
    ,PunchTime      as OutTime

From #TimeSheet as T

Where T.PunchStatus = 2
)

Select
     ROW_NUMBER() Over(Order by I.InTime) as NewRecordNum
    ,I.EmpID
    ,I.PrimaryLocation
    ,I.SubLocation
    ,I.PunchIn
    ,I.InTime
    ,Max(O.OutTime) as OutTime  

Into #Test

From cte_In as I
    Left Join cte_Out as O
        on      I.EmpID = O.EmpID
            and I.PrimaryLocation = O.PrimaryLocation
            and I.SubLocation = O.SubLocation
            and I.InTime < O.OutTime

Group by
     I.EmpID
    ,I.PrimaryLocation
    ,I.SubLocation
    ,I.PunchIn
    ,I.InTime


Select
     T.*
     ,Max(OutTime) Over (Partition by Cast(Intime as date) Order by NewRecordNum) as PriorOutPrep
     ,Lag(T.PrimaryLocation,1) Over (Partition by Cast(Intime as date) Order by NewRecordNum) as PriorPrim

Into #WindowPrep
From #Test as T

Select *,Lag(PriorOutPrep,1) Over(Partition by Cast(InTime as Date) Order by NewRecordNum) as PriorOut
Into #window
From #WindowPrep


;With cte_Final
as
(
Select
     W.EmpID
    ,W.NewRecordNum
    ,W.PriorOut

From #Window as W

Where   W.PrimaryLocation <> W.PriorPrim
    and W.InTime < W.PriorOut
)
    
Select
     W.EmpID
    ,W.PrimaryLocation
    ,W.SubLocation
    ,W.InTime
    ,W.OutTime

From #window as W
    Join cte_Final as F
        on      W.EmpID = F.EmpID
            and (
                 W.NewRecordNum = F.NewRecordNum
                 or
                 W.OutTime = F.PriorOut
                )

标签： sql-server-2019

解决方案

推荐阅读