sure thanks
Mateen
I think its very easy to do ... i just need to understand first
Let me tell you the complete scenario
A labor (id) comes in and record his attendance in front of attendance machine
the entry in the table will be like this
2020-09-01 21:05:53 01
but he is labor , he will scan again and again within minutes
and the entry will be
2020-09-01 21:05:53 01
2020-09-01 21:05:54 01
2020-09-01 21:05:55 01
and the same process for scanning out next day
2020-09-02 09:20:22 02
2020-09-02 09:22:54 02
so how to find his incoming and outgoing time
Naturally min(of incoming datetime) as indatetime
and max(of outgoing datetime) as outdatetime
but how to group by , it is datetime
to overcome this we delete all duplicate intimes
within 15 minutes range. and we keep the duplicate outtimes as it is
so after deletion it will be
2020-09-01 21:05:53 01
2020-09-02 09:20:22 02
2020-09-02 09:22:54 02
now we can pick indatetime and find the last outdatetime
but again what is the upper boundary??
our practice is that upper boundry is indatetime + 23.99 hours , nearly one day
i,e
select max(e2.entry_date_n_time)
where e2.entry_date_n_time between e.entry_date_n_time+0.01 and e.entry_date_n_time + 0.99
it all works well , untill one day a labor has to do double duty (overlapindatetime) because someone was absent
2020-09-14 19:46:44 01 came in
2020-09-15 05:00:19 02 ignore there is greater ahead
2020-09-15 05:00:21 02 pick this , because next entry is a indatetime
my query did not pick because our upper boundry was 24 hours and picked 2020-09-15 18:00:23
2020-09-15 09:14:23 01
2020-09-15 18:00:22 02 ignore
2020-09-15 18:00:23 02
for the above 6 lines my result is
2020-09-14 19:46:44 , 2020-09-15 18:00:23
2020-09-15 09:14:23 , 2020-09-15 18:00:23
for the above 6 lines the correct resul is
2020-09-14 19:46:44 , 2020-09-15 05:00:21
2020-09-15 09:14:23, 2020-09-15 18:00:23
Note:
if there is gap in data, its ok, it may be holiday or restday
or he has missed his scanning
I hope you understand now
hi
i will try to understand and come up with solution !!
please give me some time 
Please take your time.
because this is not a case of
entry and exit because of my 24 hours priority
there is a good thread you may have a look
hi mateen i tried to do this ..
SQL i have written is just for demo purposes .. feel free to modify as you please
Scenario 1 .. where data is normal
Scenario 2 .. overlap Data
; with
cte_min_status1 as
(
select
id,a.entry_date as mind
from
@sample a ,
(select min(entry_date) as mind from @sample where status = '01') b
where
a.status = '01' and datediff(ss, a.entry_date , b.mind ) between 0 and 15*60
) ,
cte_status2 as
(
select
b.id,max(a.entry_date) as maxd
from
@sample a ,
(select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date between b.mind and dateadd(ss,24*60*60 , a.entry_date )
group by b.id
) ,
cte_greater_in_Status_2 as
(
select
a.id,max(a.entry_date) as maxd_gt
from @sample a
, ( select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date > dateadd(ss,24*60*60 , b.mind )
group by a.id
)
select
'SQL Output '
, a.id
, a.mind
, case when c.maxd_gt is not null then c.maxd_gt else b.maxd end as maxd
from
cte_min_status1 a
left join
cte_status2 b on a.id = b.id
left join
cte_greater_in_Status_2 c on a.id = c.id
1 Like
hi mateen
what happened ??? ... No feedback
Some issue with my laptop. Will answer you as soon as I reach office. Please wait
OK Mateen !!
Sorry for delayed answer.
When I ran your script in my sample data given in first post , it is only producing one row.
SQL Output 0000175564 2020-09-01 09:00:23.000 2020-09-17 18:00:44.000
; with
cte_min_status1 as
(
select
id,a.entry_date_n_time as mind
from
temp_events a ,
(select min(entry_date_n_time) as mind from temp_events where status = '01') b
where
a.status = '01' and datediff(ss, a.entry_date_n_time , b.mind ) between 0 and 1560
) ,
cte_status2 as
(
select
b.id,max(a.entry_date_n_time) as maxd
from
temp_events a ,
(select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time between b.mind and dateadd(ss,246060 , a.entry_date_n_time )
group by b.id
) ,
cte_greater_in_Status_2 as
(
select
a.id,max(a.entry_date_n_time) as maxd_gt
from temp_events a
, ( select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time > dateadd(ss,2460*60 , b.mind )
group by a.id
)
select
'SQL Output '
, a.id
, a.mind
, case when c.maxd_gt is not null then c.maxd_gt else b.maxd end as maxd
from
cte_min_status1 a
left join
cte_status2 b on a.id = b.id
left join
cte_greater_in_Status_2 c on a.id = c.id
hi Mateen
that's why i said earlier
SQL i have written is just for demo purposes .. feel free to modify as you please
I will modify it ..
Yes Indeed, I will be able to modify. sure.
I got the idea,
datediff in cte is filtering desired records.
Thanks for your efforts.
Best regards.
hi mateen
please see my SQL .. i have modified it .. NOW All Records are coming but
.. please see if its what you want
please click arrow to the left for SQL
; with
cte_min_status1 as
(
select
a.id,cast(entry_date_n_time as date ) as day1,a.entry_date_n_time as mind
from
temp_events a
join
( select id, cast(entry_date_n_time as date ) as day1, min(entry_date_n_time) as mind from temp_events where status = '01'
group by id , cast(entry_date_n_time as date )
) b
on
a.id = b.id and cast(a.entry_date_n_time as date) = b.day1
where
a.status = '01' and datediff(ss, a.entry_date_n_time , b.mind ) between 0 and 1560
) ,
cte_status2 as
(
select
b.id,cast(entry_date_n_time as date ) as day1,max(a.entry_date_n_time) as maxd
from
temp_events a , (select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time between b.mind and dateadd(ss,246060 , a.entry_date_n_time )
group by
b.id, cast(entry_date_n_time as date )
) ,
cte_greater_in_Status_2 as
(
select
a.id,cast(entry_date_n_time as date ) as day1,max(a.entry_date_n_time) as maxd_gt
from
temp_events a , ( select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time > dateadd(ss,2460*60 , b.mind )
group by
a.id, cast(entry_date_n_time as date )
)
select
'SQL Output '
, a.id
, a.mind
, case when c.maxd_gt is not null then c.maxd_gt else b.maxd end as maxd
from
cte_min_status1 a
left join
cte_status2 b on a.id = b.id and a.day1 = b.day1
left join
cte_greater_in_Status_2 c on a.id = c.id and a.day1 = c.day1
thanks for your effort.
line6 where maxd is null is not correct
the following values in maxd column will move up.
line 7
mind maxd
2020/06/08 19:57:02 2020/09/08 05:05:04
one cannot go out before he comes.
This value and the following maxd value in your result should move up
and it will all be ok.
hi
here is my modified SQL and Output
please click arrow to the left for SQL
; with
cte_min_status1 as
(
select
a.id,cast(entry_date_n_time as date ) as day1,a.entry_date_n_time as mind
from
temp_events a
join
( select id, cast(entry_date_n_time as date ) as day1, min(entry_date_n_time) as mind from temp_events where status = '01'
group by id , cast(entry_date_n_time as date )
) b
on
a.id = b.id and cast(a.entry_date_n_time as date) = b.day1
where
a.status = '01' and datediff(ss, a.entry_date_n_time , b.mind ) between 0 and 1560
) ,
cte_status2 as
(
select
b.id,cast(entry_date_n_time as date ) as day1,max(a.entry_date_n_time) as maxd
from
temp_events a , (select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time between b.mind and dateadd(ss,246060 , a.entry_date_n_time )
group by
b.id, cast(entry_date_n_time as date )
) ,
cte_greater_in_Status_2 as
(
select
a.id,cast(entry_date_n_time as date ) as day1,max(a.entry_date_n_time) as maxd_gt
from
temp_events a , ( select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time > dateadd(ss,2460*60 , b.mind )
group by
a.id, cast(entry_date_n_time as date )
) ,
cte_bef_fin as
(
select
a.id, a.mind, case when c.maxd_gt is not null then c.maxd_gt else b.maxd end as maxd
from
cte_min_status1 a left join cte_status2 b on a.id = b.id and a.day1 = b.day1
left join cte_greater_in_Status_2 c on a.id = c.id and a.day1 = c.day1
)
select
a.id
, a.mind
, case when a.maxd is null then lead(maxd) over(order by cast(mind as date) ) else a.maxd end as maxd
from
cte_bef_fin a
go
1 Like
Hi harishgg1
This is working perfect, except that it is not compatible with sql server 2008. (Lead)
Anyway I now understand ideas in your code and will easily modify the code
sql 2008 compatible.
Thanks and best regards.
1 Like
Yes!!!
It will be quite helpful.
Thanks
hi here is the SQL Server 2008 solution
please click arrow to the left for 2008 SQL
; with
cte_min_status1 as
(
select
a.id,cast(entry_date_n_time as date ) as day1,a.entry_date_n_time as mind
from
temp_events a
join
( select id, cast(entry_date_n_time as date ) as day1, min(entry_date_n_time) as mind from temp_events where status = '01'
group by id , cast(entry_date_n_time as date )
) b
on
a.id = b.id and cast(a.entry_date_n_time as date) = b.day1
where
a.status = '01' and datediff(ss, a.entry_date_n_time , b.mind ) between 0 and 1560
) ,
cte_status2 as
(
select
b.id,cast(entry_date_n_time as date ) as day1,max(a.entry_date_n_time) as maxd
from
temp_events a , (select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time between b.mind and dateadd(ss,246060 , a.entry_date_n_time )
group by
b.id, cast(entry_date_n_time as date )
) ,
cte_greater_in_Status_2 as
(
select
a.id,cast(entry_date_n_time as date ) as day1,max(a.entry_date_n_time) as maxd_gt
from
temp_events a , ( select * from cte_min_status1 ) b
where
a.status = '02' and a.entry_date_n_time > dateadd(ss,2460*60 , b.mind )
group by
a.id, cast(entry_date_n_time as date )
) ,
cte_bef_fin as
(
select
row_number() over(order by cast(mind as date)) as rn , a.id, a.mind, case when c.maxd_gt is not null then c.maxd_gt else b.maxd end as maxd
from
cte_min_status1 a left join cte_status2 b on a.id = b.id and a.day1 = b.day1
left join cte_greater_in_Status_2 c on a.id = c.id and a.day1 = c.day1
)
select
a.id
, a.mind
, case when a.maxd is null then b.maxd else a.maxd end as maxd
from
cte_bef_fin a
join
cte_bef_fin b on a.rn+1= b.rn
Only 1 wrong result line number 12
2020-09-14 19:46:44 2020-09-15 18:00:23
it should be
2020-09-14 19:46:44 2020-09-15 05:00:21
I appreciate your effort.
