select distinct h.card_no,h.name,
sum(case when CONVERT(VARCHAR(100),h.ttime,108)>'08:30' and CONVERT(VARCHAR(100),h.ttime,108)<'08:40'
then 1
else 0
END) as 迟到十分钟以内次数,
sum(case when CONVERT(VARCHAR(100),h.ttime,108)>'08:40' and CONVERT(VARCHAR(100),h.ttime,108)<'09:00'
then 1
else 0
END) as 十到三十分钟之内迟到次数,
sum(case when CONVERT(VARCHAR(100),h.ttime,108)>'09:00' and CONVERT(VARCHAR(100),h.ttime,108)<'09:30'
then 1
else 0
END) as 三十分钟以上迟到次数,
sum(case when CONVERT(VARCHAR(100),h.ttime,108)>'08:30' and CONVERT(VARCHAR(100),h.ttime,108)<'09:30'
then 1
else 0
END ) as 合计迟到次数
from h
where len(h.name)>1
group by h.card_no,h.name
order by h.card_no