select 工號 id,
(case d1 when 10 then 8 when -10 then 20 when 8 then 8 else 0 end)*3600 sb1,
(case d1 when 10 then 20 when -10 then 32 when 8 then 17 else 64 end)*3600 xb1,
--...
(case d31 when 10 then 8 when -10 then 20 when 8 then 8 else 0 end)*3600 sb31,
(case d31 when 10 then 20 when -10 then 32 when 8 then 17 else 64 end)*3600 xb31
),
kq1 as (
select userid id, checktime ct, checktype type, row_number() over(order by checktime) rn
from c
),
kq2 as ( --必須保證有上班就有下班且同壹天上班只有1個，否則要去重
select t1.id, day(t1.checktime) dd, --幾號
datediff(ss, cast(convert(char(10), t1.ct, 120) as datetime, t1.ct) sb, --上班秒數
datediff(ss, cast(convert(char(10), t1.ct, 120) as datetime, t2.ct) xb --下班秒數
from kq1 t1 join kq1 t2 on t1.id=t2.id and t1.rn=t2.rn+1
where t1.type='I'
),
kq as (
select id,
max(case when dd=1 then sb end) sb1, max(case when dd=1 then xb end) xb1,
---...
max(case when dd=31 then sb end) sb2, max(case when dd=31 then xb end) xb31,
from kq2
group by id
)
select ui.userid, ui.name,
case when kq.sb1<=pb.sb1 then '準點' else '不準點' end sb1,
case when kq.xb1>=pb.xb1 then '準點' else '不準點' end xb1,
--...
case when kq.sb31<=pb.sb31 then '準點' else '不準點' end sb31,
case when kq.xb31>=pb.xb31 then '準點' else '不準點' end xb31
from userinfo ui left join pb on ui.userid=pb.id
left join kq on ui.userid=kq.id
大致如此，沒測，自己看明白後按自己需求改吧