根据首次登录和最后退出选择轮班时间

我有3个数据库,其中包括同一张表“ acc_monitor_log”(例如,员工编号作为pin)和签名时间(例如,登录和注销) acc_monitor_log示例:

    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

其中pin是员工密码,状态0是登录时间,状态1是退出状态

我们分三班工作,从上午8点到下午4点将1个“ 8小时”换成   将2个“ 8小时”从下午4点更改为晚上11点,将3个“ 9小时”从下午11点更改为   到8 AM

我尝试过的

    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
但我的结果包含许多NULL值,并且没有得到正确的时间
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