Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Converting sequential time records into IN and OUT times Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 11:07 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
Perfect. Thanks for posting that file.

Ignoring the TimeN and InOut columns that you made, here's the code to pivot (using a CROSSTAB) the pairs of times, calculate the differences between each time pair, and format the results.

WITH cteEnumerate AS
(
SELECT PayPunchID, PayrollID, FirstName, LastName, ActualTime, RoundedTime, Date,
TimeN = ROW_NUMBER() OVER (PARTITION BY PayrollID,Date ORDER BY ActualTime)-1
FROM #Temp
),
cteCrossTab AS
(
SELECT PayPunchID, PayrollID, FirstName, LastName, Date,
ActualTimeIn = MAX(CASE WHEN TimeN %2 = 0 THEN ActualTime ELSE 0 END),
ActualTimeOut = MAX(CASE WHEN TimeN %2 = 1 THEN ActualTime ELSE 0 END),
RoundedTimeIn = MAX(CASE WHEN TimeN %2 = 0 THEN RoundedTime ELSE 0 END),
RoundedTimeOut = MAX(CASE WHEN TimeN %2 = 1 THEN RoundedTime ELSE 0 END)
FROM cteEnumerate
GROUP BY PayPunchID, PayrollID, FirstName, LastName, Date, TimeN/2
)
SELECT PayPunchID, PayrollID, FirstName, LastName,
Date = CONVERT(CHAR( 7),Date ,120),
ActualTimeIn = CONVERT(CHAR( 5),ActualTimeIn ,108),
ActualTimeOut = CONVERT(CHAR( 5),ActualTimeOut ,108),
ActualDuration = CONVERT(CHAR( 5),ActualTimeOut -ActualTimeIn ,108),
RoundedTimeIn = CONVERT(CHAR( 5),RoundedTimeIn ,108),
RoundedTimeOut = CONVERT(CHAR( 5),RoundedTimeOut,108),
RoundedDuration = CONVERT(CHAR( 5),RoundedTimeOut-RoundedTimeIn,108)
FROM cteCrossTab
;


That returns the following...

PayPunchID PayrollID FirstName LastName Date    ActualTimeIn ActualTimeOut ActualDuration RoundedTimeIn RoundedTimeOut RoundedDuration
---------- --------- --------- -------- ------- ------------ ------------- -------------- ------------- -------------- ---------------
8412 73 Jone Daua 2014-03 10:56 13:57 03:01 11:00 13:45 02:45
8412 73 Jone Daua 2014-03 14:39 20:46 06:07 14:45 20:45 06:00
8412 73 Jone Daua 2014-03 07:20 10:58 03:38 07:30 10:45 03:15
8412 73 Jone Daua 2014-03 11:19 16:13 04:54 11:30 16:00 04:30
8412 73 Jone Daua 2014-03 09:51 13:33 03:42 10:00 13:30 03:30
8412 73 Jone Daua 2014-03 14:07 19:53 05:46 14:15 19:45 05:30
8412 73 Jone Daua 2014-03 09:48 13:51 04:03 10:00 13:45 03:45
8412 73 Jone Daua 2014-03 14:30 19:54 05:24 14:45 19:45 05:00
8412 73 Jone Daua 2014-03 06:48 10:21 03:33 07:00 10:15 03:15
8412 73 Jone Daua 2014-03 10:38 13:39 03:01 10:45 13:30 02:45
8412 73 Jone Daua 2014-03 14:09 17:00 02:51 14:15 17:00 02:45
8412 73 Jone Daua 2014-03 10:38 14:23 03:45 10:45 14:15 03:30
8412 73 Jone Daua 2014-03 14:54 20:20 05:26 15:00 20:15 05:15
8412 73 Jone Daua 2014-03 06:47 10:10 03:23 07:00 10:00 03:00
8412 73 Jone Daua 2014-03 10:35 13:27 02:52 10:45 13:15 02:30
8412 73 Jone Daua 2014-03 14:01 20:40 06:39 14:15 20:30 06:15



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1556191
Posted Saturday, March 29, 2014 9:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
Hi Jeff,

Thanks a lot for your post. This should work just fine. I shall try this out on our server tomorrow and update you about the results.

Cheers

Sudarshan.
Post #1556263
Posted Sunday, March 30, 2014 10:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
Hi Jeff,

Your script worked like a charm. Thanks a lot.

Sudarshan.
Post #1556324
Posted Monday, March 31, 2014 10:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
Thanks for the feedback.

Two things, though...

1. You're the one that has to support it. Do you understand how it works?
2. I'm also still greatly concerned about the people who may punch in late on one day and punch out in the wee hours of the next day which would throw this whole method on the floor. If it were me, I'd spend some time on the detection of that particular problem because it's going to happen when you least expect it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1556577
Posted Monday, March 31, 2014 4:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 4:50 PM
Points: 25, Visits: 176
Hi Jeff,

I do understand the fundamentals of your script. I understand your concern regarding employees who login in the afternoon and log out early the next day morning. We don't operate on a 24 hours basis and our stores close by around 9:00 - 9:30 PM in the normal course. On special days, when our staff are required to work late into the night, we will manually override the system hours. Since these are only 2 or 3 days in a year, we can handle this.

Thank you for highlighting the point though. This just goes to prove that you are not just helpful, you are also very professional in your approach.


Cheers

Sudarshan.
Post #1556763
Posted Monday, March 31, 2014 10:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
Thanks for the feedback. It'll let me get some sleep tonight because I was seriously trying to think of an easy way to handle that computationally but I've been doing some long days thanks to an audit at work.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1556802
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse