with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (round(datediff(current_date(),date(hire_date))/365,1) + 1)*salary/12 < 10 then 3
when (round(datediff(current_date(),date(hire_date))/365,1) + 1)*salary/12 < 20 then 2
when (round(datediff(current_date(),date(hire_date))/365,1) + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff('2026-03-09',date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff('2026-03-09',date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff('2026-03-09',date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(2026-03-09,date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(2026-03-09,date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(2026-03-09,date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (ceiling(datediff(current_date(),date(hire_date))/365) + 1)*salary/12 < 10 then 3
when (ceiling(datediff(current_date(),date(hire_date))/365) + 1)*salary/12 < 20 then 2
when (ceiling(datediff(current_date(),date(hire_date))/365) + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (floor(datediff(current_date(),date(hire_date))/365) + 1)*salary/12 < 10 then 3
when (floor(datediff(current_date(),date(hire_date))/365) + 1)*salary/12 < 20 then 2
when (floor(datediff(current_date(),date(hire_date))/365) + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary*(1+salary_increase/100)/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary*(1+salary_increase/100)/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary*(1+salary_increase/100)/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 2
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 2
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_base as
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < (select avg(avg1) from wd_base) and avg2 < (select avg(avg2) from wd_base) and avg3 < (select avg(avg3) from wd_base) then 3
when (case when avg1 < (select avg(avg1) from wd_base) then 1 end) + (case when avg2 < (select avg(avg2) from wd_base) then 1 end) + (case when avg3 < (select avg(avg3) from wd_base) then 1 end) then 1
else 0
end wd_score
from wd_base
group by 1
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_base as
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg(avg1) and avg2 < avg(avg2) and avg3 < avg(avg3) then 3
when (case when avg1 < avg(avg1) then 1 end) + (case when avg2 < avg(avg2) then 1 end) + (case when avg3 < avg(avg3) then 1 end) then 1
else 0
end wd_score
from wd_base
group by 1
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,avg(dur_kq) dur_kq
from
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
) t1
group by 1
)
,kq_score as
(
select
employee_id
,case when dur_kq < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (((datediff(current_date(),date(hire_date))/365 div(0.5)+ 1)/2+1))*salary/12 < 10 then 3
when (((datediff(current_date(),date(hire_date))/365 div(0.5)+ 1)/2+1))*salary/12 < 20 then 2
when (((datediff(current_date(),date(hire_date))/365 div(0.5)+ 1)/2+1))*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when ((datediff(current_date(),date(hire_date))/365 div(0.5)+ 1/2+1))*salary/12 < 10 then 3
when ((datediff(current_date(),date(hire_date))/365 div(0.5)+ 1/2+1))*salary/12 < 20 then 2
when ((datediff(current_date(),date(hire_date))/365 div(0.5)+ 1/2+1))*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))div(365) + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))div(365) + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))div(365) + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))div(365) + 1.5)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))div(365) + 1.5)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))div(365) + 1.5)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,ifnull(lw_score,0)score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
where stat_date >= 20250131
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats where stat_date >= 20250131) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats where stat_date >= 20250131) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats where stat_date >= 20250131) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id
with kq_base as
(
select
employee_id
,date(punch_time) dt
,timestampdiff(second, min(punch_time), max(punch_time)) dur_kq
from attendance
group by 1,2
)
,kq_score as
(
select
employee_id
,case when avg(dur_kq) < (select avg(dur_kq) avg_all from kq_base) then 1 else 0 end kq_score
from kq_base
group by 1
)
,pc_score as
(
select
id employee_id
,case when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 10 then 3
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 20 then 2
when (datediff(current_date(),date(hire_date))/365 + 1)*salary/12 < 30 then 1
else 0
end pc_score
from employees
)
,lw_score as
(
select
employee_id
,case when count(1) > 70 and sum(tag) > 25 then 3
when count(1) > 60 then 1
else 0
end lw_score
from
(
select
employee_id
,date(timestamp) dt
,case when count(1) > 3 then 1 else 0 end tag
from access_control
where hour(timestamp) not in (12,13)
group by 1,2
) t1
group by 1
)
,wd_score as
(
select
employee_id
,case when avg1 < avg_1 and avg2 < avg_2 and avg3 < avg_3then 3
when (case when avg1 < avg_1 then 1 end)+(case when avg2 < avg_2 then 1 end)+(case when avg3 < avg_3 then 1 end) = 2 then 1
else 0
end wd_score
from
(
select
employee_id
,avg(daily_interaction) avg1
,avg(daily_messages) avg2
,avg(daily_documents) avg3
from flybook_stats
where stat_date >= 20250131
group by 1
) t1
cross join
(select avg(daily_interaction) avg_1 from flybook_stats) avg_1
cross join
(select avg(daily_messages) avg_2 from flybook_stats) avg_2
cross join
(select avg(daily_documents) avg_3 from flybook_stats) avg_3
)
select
t1.employee_id
,kq_score + pc_score + ifnull(lw_score,0) + wd_score score
from kq_score t1
left join pc_score t2
on t1.employee_id = t2.employee_id
left join lw_score t3
on t1.employee_id = t3.employee_id
left join wd_score t4
on t1.employee_id = t4.employee_id