Time check if the time is late

Question

0.00/5 (No votes)

See more:

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

SQL

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

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.

Posted 7-Jun-15 23:01pm

tastini

Updated 7-Jun-15 23:15pm

Andy Lanng

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-06-11T01:27:00

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

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.

SQL

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