Matching punches to schedules and left overs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Matching punches to schedules and left overs

Author

Topic

AlexKerin
Starting Member

4 Posts

Posted - 2012-03-27 : 11:22:08

I have limited SQL knowledge, mostly hacking other people's scripts about. This is my issue. Two tables:

Punches: Column of employee IDs, column of In punches (Datetime, one per shift)
Schedule: Column of employee IDs, column of schedule starts (Datetime)

I can match a punch to a schedule by looking for the employee and an in punch within 2 hours of a schedule start:

Select
Schedule.EmployeeID,
Convert(Char,Punches.InPunchDTM,101)+Convert(Char,Punches.InPunchDTM,108) as InPunchDTM,
Convert(Char,Schedule.ShiftStartDate,101)+Convert(Char,Schedule.ShiftStartDate,108) as ShiftStartDate,
where Schedule.EmployeeID = Punches.EmployeeID
and abs(datediff(hour,S.ShiftStartDate,P.InPunchDTM))<2

This works fine. However, there are also punches that have no matching schedule - either the person came in at a different time, or was never scheduled in the first place.

How can I create a query that pulls the matched punches AND those punches that never got matched?

Many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 11:53:08

whats the matching rule? what if you've multiple punches within schedule? ie person taking breaks in between schedule?

The best thing would be to put together some sample data for your scenario and explain what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlexKerin
Starting Member

4 Posts

Posted - 2012-03-27 : 12:19:03

Thanks for the response. The situations you mention do occur, but I wanted to attack one issue at a time - don't know if that makes sense. The matching rule is an in punch within 2 hours of a schedule start (either way, later or earlier) and the same employee ID.

I don't know if this is the best format for the data, but:

Punches:

EmployeeID InPunch
1 5/2/2011 7:26
2 5/2/2011 12:29
1 5/3/2011 7:28
2 5/4/2011 7:27
1 5/4/2011 12:30
2 5/5/2011 7:26
3 5/5/2011 12:57
1 5/6/2011 7:26
2 5/9/2011 7:27
5 5/9/2011 12:59
6 5/10/2011 7:27

Schedule:

EmployeeID ShiftStart
1 5/2/2011 7:00
2 5/2/2011 22:00
1 5/3/2011 7:30
2 5/4/2011 7:30
1 5/4/2011 12:45
2 5/5/2011 5:29
3 5/5/2011 12:00
1 5/6/2011 7:30

Result wanted:

Employee ID InPunch ShiftStart
1 5/2/2011 7:26 5/2/2011 7:00
2 5/2/2011 12:29
1 5/3/2011 7:28 5/3/2011 7:30
2 5/4/2011 7:27 5/4/2011 7:30
1 5/4/2011 12:30 5/4/2011 12:45
2 5/5/2011 7:26 5/5/2011 5:29
3 5/5/2011 12:57 5/5/2011 12:00
1 5/6/2011 7:26 5/6/2011 7:30
2 5/9/2011 7:27
5 5/9/2011 12:59
6 5/10/2011 7:27

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 12:25:42

quote:
Originally posted by AlexKerin

Thanks for the response. The situations you mention do occur, but I wanted to attack one issue at a time - don't know if that makes sense. The matching rule is an in punch within 2 hours of a schedule start (either way, later or earlier) and the same employee ID.

I don't know if this is the best format for the data, but:

Punches:

EmployeeID InPunch
1 5/2/2011 7:26
2 5/2/2011 12:29
1 5/3/2011 7:28
2 5/4/2011 7:27
1 5/4/2011 12:30
2 5/5/2011 7:26
3 5/5/2011 12:57
1 5/6/2011 7:26
2 5/9/2011 7:27
5 5/9/2011 12:59
6 5/10/2011 7:27

Schedule:

EmployeeID ShiftStart
1 5/2/2011 7:00
2 5/2/2011 22:00
1 5/3/2011 7:30
2 5/4/2011 7:30
1 5/4/2011 12:45
2 5/5/2011 5:29
3 5/5/2011 12:00
1 5/6/2011 7:30

Result wanted:

Employee ID InPunch ShiftStart
1 5/2/2011 7:26 5/2/2011 7:00
2 5/2/2011 12:29
1 5/3/2011 7:28 5/3/2011 7:30
2 5/4/2011 7:27 5/4/2011 7:30
1 5/4/2011 12:30 5/4/2011 12:45
2 5/5/2011 7:26 5/5/2011 5:29
3 5/5/2011 12:57 5/5/2011 12:00
1 5/6/2011 7:26 5/6/2011 7:30
2 5/9/2011 7:27
5 5/9/2011 12:59
6 5/10/2011 7:27


SELECT EmployeeID, InPunch, ShiftStart
FROM Punches p
OUTER APPLY (SELECT TOP 1 ShiftStart
             FROM Schedule
             WHERE EMployeeID = p.EmployeeID
             AND ABS(DATEDIFF(minute,ShiftStart,p.InPunch)/60)<=2
             ORDER BY ShiftStart DESC)s

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlexKerin
Starting Member

4 Posts

Posted - 2012-03-27 : 12:54:30

Ha! Apply Outer. That looks ideal - never heard of it before. I will take this to my actual data set and see where I get to.

Would it be best to start a new thread with new data for the other issues I face (multiple punches for a shift - need to capture just the first punch and last punch of the shift, need to calculate the total time between punches for a single shift)?

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 14:54:39

nope..please post it in same thread as that will improve continuity for anyone whozz looking into the issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 14:55:16

Also see what all you can do with apply oprator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlexKerin
Starting Member

4 Posts

Posted - 2012-03-27 : 15:38:51

quote:
Originally posted by visakh16

Also see what all you can do with apply oprator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Very interesting - given the issues I face, scenarios 1 to 3 could all be applicable. I will post new data and issues soon.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 17:05:29

cool.. will wait for that...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources