Click here to Skip to main content
14,694,714 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


What I have tried:

<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
but i have result induded many NULL value and don't get correct time

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
Posted
Updated 9-May-20 4:07am

1 solution

   
Comments
MostafaAdel89 9-May-20 9:34am
   
i don't know employee shift i want to detect employee shift from sign in and out
MostafaAdel89 9-May-20 9:36am
   
in this example he is create ShiftStartMinutesFromMidnight cloumn and connected it with userid

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900