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.
x
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.
Advertisement
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