Выберите время смены на основе первого входа и последнего выхода

---

I have 3 databases include the same table "acc_monitor_log" such as employee id as pin and sign time such as sign in and sign out

acc_monitor_log example

time	pin	state
2020-05-05 08:14:47.000	240	0
2020-05-05 15:32:21.000	240	1
2020-05-06 08:04:32.000	240	0
2020-05-06 08:04:37.000	240	0
2020-05-06 09:39:16.000	240	1
2020-05-06 10:04:17.000	240	0
2020-05-06 15:28:54.000	240	1

where pin is employee code and state 0 is sign in time and 1 is sign out 

and we work in 3 shifts, 
shift 1 "8 hours" from 8 AM to 4 PM and 
shift 2 "8 hours" from 4 PM to 11 PM and 
shift 3 "9 hours" from 11 PM to 8 AM

i want to get first sign in and last sign out 
and select shift based on time

Что я уже пробовал:

<pre>
declare 
@tbl table(pin nvarchar(50),_shift nvarchar(20),_in datetime,_out datetime)
declare 
@tblfinal table(serial int,pin nvarchar(50),_shift nvarchar(20),dd int,mm int,_in datetime,_out datetime)
declare 
@shift1start time = '06:30:00',
@shift1end time ='20:30:00',
@shift2start time ='13:00:00',
@shift2end time = '09:30:00',
@startdate date = '2020-04-25',
@enddate date = '2020-04-25'  

while @startdate != '2020-05-08'
begin

begin
with cte_ as (
SELECT  abc.pin , 
(SELECT min(time)
FROM [TUBE1].[dbo].[acc_monitor_log] 
where convert(date,time) between @startdate and @enddate and state =0 and pin=abc.pin)_in,
(SELECT max(time) FROM [TUBE1].[dbo].[acc_monitor_log]
where convert(date,time) between @startdate and @enddate
and state = 1 and pin=abc.pin)_out
FROM [TUBE1].[dbo].[acc_monitor_log] abc
where convert(date,time) between @startdate and @enddate
group by pin)
,

cte_2 as (
SELECT abc.pin ,
--min(time) _in,
(SELECT min(time)
FROM [TUBE2].[dbo].[acc_monitor_log] 
where convert(date,time) between @startdate and @enddate
and state =1
and pin=abc.pin)_in,
(SELECT max(time)
FROM [TUBE2].[dbo].[acc_monitor_log]
where convert(date,time) between @startdate and @enddate
and state = 0
and pin=abc.pin)_out
FROM [TUBE2].[dbo].[acc_monitor_log] abc
where convert(date,time) between @startdate and @enddate
group by pin),

--order by pin asc

cte_3 as (
SELECT abc.pin ,
--min(time) _in,
(SELECT min(time)
FROM [TUBE3].[dbo].[acc_monitor_log] 
where convert(date,time) between @startdate and @enddate
and state =0
and pin=abc.pin)_in,
--max(time) _out
(SELECT max(time)
FROM [TUBE3].[dbo].[acc_monitor_log]
where convert(date,time) between @startdate and @enddate
and state = 1
and pin=abc.pin)_out
FROM [TUBE3].[dbo].[acc_monitor_log] abc
where convert(date,time) between @startdate and @enddate
--and min(time) having state=0
--and pin=10
group by pin)

--order by pin asc
insert into @tbl 

select cte_.pin,
case 
when convert(time,cte_._in) between @shift1start and @shift1end 
and  convert(time,cte_._out) between @shift1start and @shift1end then '1'
when convert(time,cte_._in) between @shift2start and @shift2end 
and  convert(time,cte_._out) between @shift2start and @shift2end then '2'
else 'unkown'

end as _sift,
cte_._in,cte_._out 
from cte_

union

select cte_2.pin,
case
when convert(time,cte_2._in) between @shift1start and @shift1end 
and  convert(time,cte_2._out) between @shift1start and @shift1end then '1'
when convert(time,cte_2._in) between @shift2start and @shift2end 
and  convert(time,cte_2._out) between @shift2start and @shift2end then '2'
else 'unkown'
end as _sift,
cte_2._in,cte_2._out 
from cte_2

union

select cte_3.pin,
case 
when convert(time,cte_3._in) between @shift1start and @shift1end 
and convert(time,cte_3._out) between @shift1start and @shift1end then '1'
when convert(time,cte_3._in) between @shift2start and @shift2end 
and  convert(time,cte_3._out) between @shift2start and @shift2end then '2'
else 'unkown'
end as _sift,
cte_3._in,cte_3._out 
from cte_3
order by pin
 
end	
set @startdate = dateadd(day,1,@startdate)
set @enddate = dateadd(day,1,@enddate)
end

declare @pinn int = 0
while @pinn <=600
begin
insert into @tblfinal
select row_number() over (partition by dd,mm order by dd,mm)as serial,* from 
(select pin,_shift,datepart(day,_in) dd,datepart(MONTH,_in) mm,_in,_out from @tbl )a
where pin = @pinn

set @pinn = @pinn+1
end


select pin,_shift,min(_in) _in_,max(_out) _out_ from
(
select RIGHT('0000'+ISNULL(pin,''),4) as pin,_shift,_in,convert(date,_in)datein,_out ,convert(date,_out) dateout
from @tblfinal 
where serial = 1

)aa
where (datein = dateout or dateout = dateadd(day,1,datein) or datein is null or dateout is null)
group by pin,_shift,case when datein is not null then datein else dateout end
order by pin

но у меня есть результат индуцированный многими нулевыми значениями и не получаю правильного времени

0006	1	2020-04-26 14:53:54.000	2020-04-26 08:08:03.000
0006	1	2020-05-03 14:52:24.000	2020-05-03 07:59:05.000
0006	unkown	2020-04-25 14:59:34.000	NULL
0006	unkown	NULL	2020-04-27 07:54:17.000
0006	unkown	2020-05-02 15:10:04.000	NULL
0006	unkown	2020-05-06 15:04:28.000	NULL

---