I set up some sample code like this
declare @demo table (userid int, checktime datetime);
insert into @demo (userid, checktime) values
(1,'2022-10-04 10:25:44.000')
,(1,'2022-10-04 10:25:46.000')
,(1,'2022-10-04 10:30:55.000')
,(1,'2022-10-04 11:39:23.000')
,(2,'2022-10-07 14:05:22.000')
,(2,'2022-10-08 09:19:58.000')
,(2,'2022-10-08 15:34:53.000');
By using the
LEAD [
^] function I can get the next datetime in the list for every row and for each
userid
..
select
userid, checktime,
LEAD(checktime, 1)
OVER (partition by userid order by userid, checktime) as nextcheck
from @demo;
Note the
partition by
in the
over
clause.
That gives me these results:
userid checktime nextcheck
1 2022-10-04 10:25:44.000 2022-10-04 10:25:46.000
1 2022-10-04 10:25:46.000 2022-10-04 10:30:55.000
1 2022-10-04 10:30:55.000 2022-10-04 11:39:23.000
1 2022-10-04 11:39:23.000 NULL
2 2022-10-07 14:05:22.000 2022-10-08 09:19:58.000
2 2022-10-08 09:19:58.000 2022-10-08 15:34:53.000
2 2022-10-08 15:34:53.000 NULL
As you can see there is duplication -
nextcheck
on row 1 becomes
checktime
on row 2. You can use a combination of the
ROW_NUMBER[
^] function and the
% (Modulus)[
^] operator to correct that. E.g.
;with cte as
(
select
userid, checktime,
LEAD(checktime, 1)
OVER (partition by userid order by userid, checktime) as nextcheck,
Row_number() OVER (partition by userid order by userid, checktime) as rn
from @demo
)
select userid, checktime as checkin, nextcheck as checkout
from cte
where rn % 2 = 1;
which gives these results
userid checkin checkout
1 2022-10-04 10:25:44.000 2022-10-04 10:25:46.000
1 2022-10-04 10:30:55.000 2022-10-04 11:39:23.000
2 2022-10-07 14:05:22.000 2022-10-08 09:19:58.000
2 2022-10-08 15:34:53.000 NULL
Points to note:
- This assumes your equipment is working well and that your users are disciplined about checking in and checking out.
- note that user 2 checked in but has not checked out - hence the NULL value
- you mentioned in a comment
Quote:
i want to use only time to first checkin and last checkout.
which could done by a simple group by e.g.
select userid, MIN(checktime) as checkin, MAX(checktime) as checkout
from @demo
group by userid;
giving
userid checkin checkout
1 2022-10-04 10:25:44.000 2022-10-04 11:39:23.000
2 2022-10-07 14:05:22.000 2022-10-08 15:34:53.000
But note that for userid = 2 that is actually not a checkout time but the last checkin time (and they are still there).
So, you probably want to do the full list of checkin/checkout first (as above) and then do the min/max on those results.
There should be enough here for you to work it out
EDIT after OP Comment. The following query will generate a list of all dates between two dates, then highlight where dates are missing in the @demo table above
declare @startdate date = '2022-10-04';
declare @enddate date = '2022-10-10';
WITH q AS
(
SELECT @startdate AS datum
UNION ALL
SELECT dateadd(DAY, 1,datum)
FROM q
WHERE dateadd(DAY, 1,datum) < @enddate
)
SELECT datum, d.userid, d.checktime
FROM q
left outer join @demo d on CAST(d.checktime as date) = q.datum;
If d.userid is null then there was no data for that date.
It's a good idea to have a permanent table of dates generated like this - the join would be the same. That way you can mark up non-working days quite easily - see this link for further information
Creating a date dimension or calendar table in SQL Server[
^]