Find start date and end date from the employee time clock

vacation days calculator excel
how many days is 40 hours of pto
pto calculator for hourly employees
paid time off for hourly employees
vacation accrual tables
how to calculate pto for part-time employees
pto payout calculator
calculate 1 hour for every 30 hours worked

I am trying to find out star date and end date for the employee time clock. Following is the example of the data I have.

PersonID  EventTime                       ActivityCode
1         2019-07-29 00:03:03.000         7
1         2019-07-29 00:09:41.000         0
1         2019-07-29 00:01:07.000         7
1         2019-07-29 00:01:19.000         0

Output wanted:

PersonID  EventTime                   EndTime                           ActivityCode
1         2019-07-29 00:03:03.000     2019-07-29 00:09:41.000            7
1         2019-07-29 00:09:41.000     2019-07-29 00:01:07.000            0
1         2019-07-29 00:01:07.000      2019-07-29 00:01:19.000           7
1         2019-07-29 00:01:19.000                                        0

I have to find the start date and end date of the person based on eventtime and activitycode. The start date will be event time column and I have to calculate end date from the same column which is next eventtime in the row.

I've tried using lead function in T-SQL but it's not giving me the result I want.

select personid, activitycode, eventtime as starttime
    , (lead(eventtime) over (order by personid)) as endtime 
from ActivityTable

If anyone has some idea how to deal with this, I would really appreciate it.


I suspect your EventTime should be 13:00 rather than 01:00

Example

Declare @YourTable Table ([PersonID] varchar(50),[EventTime] datetime,[ActivityCode] int)  Insert Into @YourTable Values 
 (1,'2019-07-29 00:03:03.000',7)
,(1,'2019-07-29 00:09:41.000',0)
,(1,'2019-07-29 00:13:07.000',7)  << changed to 13
,(1,'2019-07-29 00:13:19.000',0)  << changed to 13

Select PersonID
      ,EventTime 
      ,EndTime= lead([EventTime],1) over (partition by [PersonID] order by [EventTime])
      ,ActivityCode
 From @YourTable
 Order by EventTime

Returns

PersonID    EventTime                 EndTime                   ActivityCode
1           2019-07-29 00:03:03.000   2019-07-29 00:09:41.000   7
1           2019-07-29 00:09:41.000   2019-07-29 00:13:07.000   0
1           2019-07-29 00:13:07.000   2019-07-29 00:13:19.000   7
1           2019-07-29 00:13:19.000   NULL                      0

OnTheClock - Simplfied Employee Time Tracking, Here is a Free PTO calculator for you to use, it will calculate sick time off, “Start Date” and “End Date” - Generally this would be an entire year, but may be a  Make Your Workplace more Efficient. Track Time Digitally with Modern Time & Attendance. Speak with Experts & Compare Features Like Biometric Time Clocks & Online Time Sheets


Your output looks kind of weird in your example since your EventTime an seem to happen after your EndTime. If you are looking for a ordering of time and are expecting a result set like this:

Then xQbert's comment is correct. You only need to add EventTime in your Order By. See example code below.

CREATE TABLE #T (
    PersonID INT
    ,EventTime DATETIME
    ,ActivityCode INT
)

INSERT INTO #T(
    PersonID
    ,EventTime
    ,ActivityCode
)
SELECT
    PersonID
    ,EventTime
    ,ActivityCode
FROM
    (VALUES ( 1,'2019-07-29 00:03:03.000', 7)
            ,(1,'2019-07-29 00:09:41.000', 0)
            ,(1,'2019-07-29 00:01:07.000', 7)
            ,(1,'2019-07-29 00:01:19.000', 0)) AS T (PersonID,EventTime,ActivityCode)


SELECT
    PersonID
    ,ActivityCode
    ,EventTime AS starttime
    ,(LEAD(EventTime) OVER (ORDER BY PersonID, EventTime DESC)) AS endtime
FROM
    #T;

[PDF] Quick guide to ExecuTime Employee Actions, Clock In/Out – Hourly Part-Time & Workstudy Employees: Clock In Either key in the “Start Date” and “End Date” or click on the CALENDAR icon to choose the submit a time-off request in ExecuTime in order to get paid for the hours they are​  99% Match on Time Clock For Employees. Start searching with Visymo.com.


Try Like This..

  CREATE TABLE #T 
 (
PersonID INT
,EventTime DATETIME
,ActivityCode INT
)



INSERT INTO #T(
PersonID
,EventTime
,ActivityCode
)
SELECT
PersonID
,EventTime
,ActivityCode
FROM
(VALUES ( 1,'2019-07-29 00:03:03.000', 7)
        ,(1,'2019-07-29 00:09:41.000', 0)
        ,(1,'2019-07-29 00:13:07.000', 7)
        ,(1,'2019-07-29 00:13:19.000', 0)) AS T (PersonID,EventTime,ActivityCode)

SELECT  PersonID,EventTime,ActivityCode,
    (SELECT MIN(EventTime) FROM #T T2 WHERE T1.PersonID = T2.PersonID AND 
    T1.EventTime < T2.EventTime)
  FROM  #T T1

Time Clock report, This report gives the clock in and clock out times for your employees that use the Time Clock. If you're familiar with the Time Clock report, you can find quick run instructions, The report will give you details for the staff member, with date, time​, hours worked and rate Start and End Dates, Choose the start and end dates. Search for Employee clock in online. Find it with us!


Field Employee Time Sheets and Time Clock, You can also search for reports of your employee's time off. Search by service provider, reporting period, or start/end date. Once you have completed your search,  Stop wasting money on overtime. When I Work is free. Get started today! Integrates with staff schedules & payroll to save you time & money.


[PDF] ADP TIME DETAIL REPORT, Instructions on how to run a Time Detail report in ADP. This report produces the total number of hours worked by your employees for any given dates. #10 - Enter the desired “Start Date” and “End Date” for your report and click “OK.” You may calculate the total dollars paid by multiplying the total hours worked (​found at. I am trying to find out star date and end date for the employee time clock. Following is the example of the data I have. PersonID EventTime ActivityCode 1 2019-07-29 00:03:03.000 7 1 2019-07-29 00:09:41.000 0 1 2019-07-29 00:01:07.000 7 1 2019-07-29 00:01:19.000 0


All about the employee Time Clock – Customer Feedback , Add a time clock entry. Time Clock entries can be added from Users or from the Time Clock by date. In the Start Time field, enter the required date and time. 5. If the staff member is still working, uncheck the End Time box. 6. If the staff Find the time clock entry you need to edit whichever way you prefer. 2. Double-click  For those who need more control over editing and copying employee shifts, we offer an advanced set of controls which allow you to specify unique start and end shift dates/times. Copying a shift to other employees and dates can also be accomplished here. Employee scheduling could never be easier!