I want to get rotational shift in-out result

Question

1.00/5 (1 vote)

See more:

SQL-Server-2014

I want to get rotational shift In-Out result
Hello Everyone,

Need Help for below.

I want to get rotational shift In-Out result.

My Table is as below :

MachinePunchingData
sEnrollNumber iDateTime
3 2018-04-03 07:11:10.000
3 2018-04-03 23:11:19.000
3 2018-04-03 23:40:29.000
3 2018-04-04 00:11:32.000
3 2018-04-04 01:11:34.000
3 2018-04-04 05:12:09.000
3 2018-04-04 07:10:26.000
3 2018-04-04 23:04:28.000

ShiftMaster
ShiftID ShiftStartTime ShiftEndTime TotalTime IsRotational
1 23:00:00 07:00:00 08:00:00 true

EmpMaster
EmpID PunchID(sEnrollNumber) Fname Lname IsActive ShiftID
1 3 Xyz Xyz true 1
I want below result:
sEnrollNumber iDateTime In/Out AttnDate
3 2018-04-03 07:11:10.000 Out 2018-04-02
3 2018-04-03 23:11:19.000 In 2018-04-03
3 2018-04-03 23:40:29.000 Out 2018-04-03
3 2018-04-04 00:11:32.000 In 2018-04-03
3 2018-04-04 01:11:34.000 Out 2018-04-03
3 2018-04-04 05:12:09.000 In 2018-04-03
3 2018-04-04 07:10:26.000 Out 2018-04-03
3 2018-04-04 23:04:28.000 In 2018-04-04

Problems :

How i will identify which one for In and Which one for Out.
Emplolyee may be come earlier then his shift time like 22:00:00
And may be go at 06:00:00
Employee may be come late : 00:11:01
Go late : 08:11:23
Request you all if you get any idea or solution for this then help me.

Thanks,
Chintan

What I have tried:

select
ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC) [PunchNum],sEnrollNumber,
iDatetime,
Cast(iDatetime as date) as date2,
Case when
Cast([iDateTime] as time) > Cast((select dateadd(mi,-60,StartTime) from shiftMaster where ShiftID=(select ShiftID1 from EmployeeMaster where PunchID=sEnrollNumber)) as time)
and Cast([iDateTime] as time) < Cast((select dateadd(mi,59,StartTime) from shiftMaster where ShiftID=(select ShiftID1 from EmployeeMaster where PunchID=sEnrollNumber)) as time)
and ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2)=0
then 'IN' Else 'OUT' End as 'In/Out' from
PunchingData
where [sEnrollNumber]=3 and iDateTime between '2018-04-01' and '2018-04-30'

Posted 27-May-18 20:36pm

Member 13165845

Updated 30-May-18 6:25am

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 · 2018-05-30T06:27:00

You had the right idea using ROW_NUMBER and modulus (%) you were just slightly over-complicating things.
This query gets the data in a tidy format

SQL

select E.PunchID, iDateTime,
CASE WHEN ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2) = 0 THEN 'IN' ELSE 'OUT' END as InOut,
 Cast(iDatetime as date) as date2, ShiftId
from #EmpMaster E
inner join #MachinePunchingData M ON E.PunchID=M.sEnrollNumber
ORDER BY iDateTime

Note I've removed the checks on Punch Number and Dates for now to make things clearer, but included the Shift Id (why will become apparent) Result:

SQL

3	2018-04-03 07:11:10.000	OUT	2018-04-03  1
3	2018-04-03 23:11:19.000	IN	2018-04-03  1
3	2018-04-03 23:40:29.000	OUT	2018-04-03  1
3	2018-04-04 00:11:32.000	IN	2018-04-04  1
3	2018-04-04 01:11:34.000	OUT	2018-04-04  1
3	2018-04-04 05:12:09.000	IN	2018-04-04  1
3	2018-04-04 07:10:26.000	OUT	2018-04-04  1
3	2018-04-04 23:04:28.000	IN	2018-04-04  1

Now is the time to look at your data ... that employee seems to be in and out of the workplace many times during their shift! You might want to start with some simpler data first - exactly one entry and exactly one exit per shift.

However, sticking with what we have, you are going to want to know what time they came in, what time they went out and how long they worked, preferably all on the same row. You can use one of the SQL Analytic Functions [^] to do that. I'm going to use LEAD to get the "next" time

SQL

select E.PunchID,
    idatetime as time1,
    LEAD(iDatetime,1) OVER (partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC) as time2,
    CASE WHEN ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2) = 0 THEN 'IN' ELSE 'OUT' END as InOut,
    Cast(iDatetime as date) as date2
    ,E.ShiftID
from #EmpMaster E
inner join #MachinePunchingData M ON E.PunchID=M.sEnrollNumber

Results:

3	2018-04-03 07:11:10.000	2018-04-03 23:11:19.000	OUT	2018-04-03	1
3	2018-04-03 23:11:19.000	2018-04-03 23:40:29.000	IN	2018-04-03	1
3	2018-04-03 23:40:29.000	2018-04-04 00:11:32.000	OUT	2018-04-03	1
3	2018-04-04 00:11:32.000	2018-04-04 01:11:34.000	IN	2018-04-04	1
3	2018-04-04 01:11:34.000	2018-04-04 05:12:09.000	OUT	2018-04-04	1
3	2018-04-04 05:12:09.000	2018-04-04 07:10:26.000	IN	2018-04-04	1
3	2018-04-04 07:10:26.000	2018-04-04 23:04:28.000	OUT	2018-04-04	1
3	2018-04-04 23:04:28.000	NULL	IN	2018-04-04	1

Notice we don't have what time he left that last shift!

You're going to want to expand on this basic data to be able to get real information out of it, and notice that you're really only interested in the rows that are "IN" (the "OUT" rows actually show how long the employee was not in work). For that I'm going to suggest you use a Common Table Expression (CTE) ... it helps to make complex queries clearer and means you can build up the query in steps, checking your results as you go

SQL

;with cte AS
(
	select E.PunchID, 
		idatetime as time1,
		LEAD(iDatetime,1) OVER (partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC) as time2,
		CASE WHEN ((ROW_NUMBER() OVER(partition BY [sEnrollNumber] ORDER BY [iDateTime] ASC))%2) = 0 THEN 'IN' ELSE 'OUT' END as InOut,
		Cast(iDatetime as date) as date2
		,E.ShiftID
	from #EmpMaster E
	inner join #MachinePunchingData M ON E.PunchID=M.sEnrollNumber
)
SELECT PunchID, time1,time2, date2
from cte
WHERE InOut = 'IN'
ORDER BY time1

Results:

3	2018-04-03 23:11:19.000	2018-04-03 23:40:29.000	2018-04-03
3	2018-04-04 00:11:32.000	2018-04-04 01:11:34.000	2018-04-04
3	2018-04-04 05:12:09.000	2018-04-04 07:10:26.000	2018-04-04
3	2018-04-04 23:04:28.000	NULL	2018-04-04

That should keep you going for a while.