时间检查是否时间已晚 [英] Time check if the time is late

问题描述

时间检查是否迟到



查看更多：SQL

i有如下数据，需要检查时间来自其他桌子。

三张桌子。

1.EmpShift - > EmpID，ShiftName，Startdate，EndDate

2.ShiftTable - > ShiftName，StartTime，EndTime

3.InOut - > EmpID，InOut，CheckType。



到目前为止我只尝试这个。



隐藏复制代码

Time Check Whether it is late or not

See more: SQL
i have some data like below , need to check the time from other table.
three tables.
1.EmpShift -> EmpID,ShiftName,Startdate,EndDate
2.ShiftTable ->ShiftName,StartTime,EndTime
3.InOut -> EmpID,InOut,CheckType.

So far i try only this.

Hide Copy Code

Select EmpShift.EmpID,EmpShift.ShiftName,EmpShift.StartDate,EmpShift.EndDate,
        CONVERT(VARCHAR(10), InOut.inout, 112) AS WORKDATE, convert(char(5), InOut.inout, 108) As CheckTime,
        InOut.CheckType
  From EmpShift
       Inner Join InOut On
EmpShift.EmpID=InOut.EmpID
Where (StartDate Between '2015-05-01' AND '2015-06-10') AND (EndDate Between '2015-05-01' AND '2015-06-10')
GROUP BY EmpShift.EmpID, CONVERT(VARCHAR(10),InOut.inout, 112),convert(char(5), InOut.inout, 108),EmpShift.EmpID,EmpShift.ShiftName,
EmpShift.StartDate,EmpShift.EndDate,InOut.CheckType

结果是这样的。



隐藏复制代码

result is like this.

Hide Copy Code

191 Evening 2015-05-21  2015-05-29  20150507    19:05   O
191 Morning 2015-05-01  2015-05-20  20150507    19:05   O
191 Morning 2015-05-30  2015-06-07  20150507    19:05   O
191 Evening 2015-05-21  2015-05-29  20150509    11:05   I
191 Morning 2015-05-01  2015-05-20  20150509    11:05   I
191 Morning 2015-05-30  2015-06-07  20150509    11:05   I
191 Evening 2015-05-21  2015-05-29  20150509    15:02   I
191 Morning 2015-05-01  2015-05-20  20150509    15:02   I
191 Morning 2015-05-30  2015-06-07  20150509    15:02   I

我的意思是INO意味着出去我想要得到的是我想用ShiftTable检查时间和输出和每天迟到的分钟数。如何在checktype之后得到这个结果。

I means "IN" O means "Out" what i am trying to get is i want to check the time with ShiftTable and output and how many minutes is the late for each day. how i can get this result in after the checktype.

推荐答案

如果你将原始查询放入公用表表达式（CTE），你可以轻松地将它加入到ShiftTable中例如

If you put your original query into a Common Table Expression (CTE) you can easily join it to your ShiftTable e.g.

;WITH CTE AS
(
	Select EmpShift.EmpID,EmpShift.ShiftName,EmpShift.StartDate,EmpShift.EndDate,
			CONVERT(VARCHAR(10), InOut.inout, 112) AS WORKDATE, convert(char(5), InOut.inout, 108) As CheckTime,
			InOut.CheckType
	  From EmpShift
		   Inner Join InOut On
	EmpShift.EmpID=InOut.EmpID
	Where (StartDate Between '2015-05-01' AND '2015-06-10') AND (EndDate Between '2015-05-01' AND '2015-06-10')
	GROUP BY EmpShift.EmpID, CONVERT(VARCHAR(10),InOut.inout, 112),convert(char(5), InOut.inout, 108),EmpShift.EmpID,EmpShift.ShiftName,
	EmpShift.StartDate,EmpShift.EndDate,InOut.CheckType
)
select * 
from CTE
INNER JOIN ShiftTable ST ON CTE.ShiftName = ST.ShiftName

将把ShiftTable StartTime 和 EndTime 添加到上面的结果中。



您需要做的就是在您的选择中添加一个比较适当的列

which will add the ShiftTable StartTime and EndTime to your results above.

All you need to do then is add a comparison to the appropriate column in your select e.g.

CASE WHEN CTE.CheckType = 'O' THEN
    ABS(DATEDIFF(MINUTE, CTE.CheckTime, ST.EndTime))
ELSE
    ABS(DATEDIFF(MINUTE, CTE.CheckTime, ST.StartTime))
END as TimeDiff,
CASE WHEN CTE.CheckType = 'O' THEN
    CASE WHEN CTE.CheckTime > ST.EndTime THEN 'Worked Late'
         WHEN CTE.CheckTime < ST.EndTime THEN 'Left Early'
         ELSE 'On Time'
    END
ELSE
    CASE WHEN CTE.CheckTime > ST.StartTime THEN 'Arrived Late'
         WHEN CTE.CheckTime < ST.StartTime THEN 'Started Early'
         ELSE 'On Time'
    END
END As EarlyLate

这篇关于时间检查是否时间已晚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！