I want to separate checkinout table import from zk attendance log in SQL

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[^]

Umair	77	1	2022-11-16	2022-11-16 09:41:25.000	2022-11-16 18:45:46 9:4
Umair	77	1	2022-11-21	2022-11-21 09:29:29.000	2022-11-21 19:00:33	9:31
Umair	77	1	2022-11-25	2022-11-25 09:31:00.000	NULL	NULL
Umair	77	1	2022-11-26	2022-11-26 10:42:55.000	NULL	NULL

Umair	77	1	2022-11-16	2022-11-16 09:41:25.000	2022-11-16 18:45:46 9:4
Umair	77	1	2022-11-16	2022-11-17 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-18 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-19 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-20 NULL   NULL NULL
Umair	77	1	2022-11-21	2022-11-21 09:29:29.000	2022-11-21 19:00:33	9:31
Umair	77	1	2022-11-16	2022-11-22 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-23 NULL   NULL NULL	NULL

select u.NAME,u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
    select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
    ,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
        from(
            select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
            ,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
            ,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut
           
 
            from CHECKINOUT c
            group by c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW,checktime)
        )temp
) attendance 
inner join userinfo u on u.USERID = attendance.USERID where u.USERID =77