Find start date and end date from the employee time clock

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.

Answer

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