select 工号,姓名,
sum(case when 刷卡时间 between '07:00' and '10:00' then 1 else 0 end) as [zao],
sum(case when 刷卡时间 between '11:00' and '13:00' then 1 else 0 end) as [zhong],
sum(case when 刷卡时间 between '16:30' and '19:00' then 1 else 0 end) as [wan]
from tb where datepart(month,刷卡日期)=2 group by 工号,姓名
select 工号,姓名,
sum(case when convert(datetime,刷卡日期+' '+刷卡时间) between convert(datetime,刷卡日期+' 07:00') and convert(datetime,刷卡日期+' 10:00') then 1 else 0 end)[早餐 07:00-10:00],
sum(case when convert(datetime,刷卡日期+' '+刷卡时间) between convert(datetime,刷卡日期+' 11:00') and convert(datetime,刷卡日期+' 13:00') then 1 else 0 end)[中餐 11:00-13:00],
sum(case when convert(datetime,刷卡日期+' '+刷卡时间) between convert(datetime,刷卡日期+' 16:30') and convert(datetime,刷卡日期+' 19:00') then 1 else 0 end)[晚餐 16:30-19:00]
from tb
where convert(varchar(6),convert(datetime,刷卡日期),112) = '201102'
group by 工号,姓名
CREATE TABLE #t(工号 int,姓名 varchar(10),刷卡日期 smalldatetime,刷卡时间 time,机器编号 int);
INSERT INTO #t(工号,姓名,刷卡日期,刷卡时间,机器编号) values
(1, '张三', '2011-02-01', '17:30', 1),
(2, '李四', '2011-02-01', '12:30', 2),
(2, '李四', '2011-02-02', '17:30', 1),
(1, '张三', '2011-02-01', '07:30', 1),
(2, '李四', '2011-02-01', '07:30', 2),
(1, '张三', '2011-02-02', '12:30', 1);
-- SELECT * FROM #t;
SELECT 工号,姓名,'2011-02' AS 月分,SUM(CASE WHEN 刷卡时间 >='07:00' AND 刷卡时间<'10:00' THEN 1 ELSE 0 END) AS 早餐
,SUM(CASE WHEN 刷卡时间 >='11:00' AND 刷卡时间<'13:00' THEN 1 ELSE 0 END) AS 中餐
,SUM(CASE WHEN 刷卡时间 >='16:30' AND 刷卡时间<'19:00' THEN 1 ELSE 0 END) AS 晚餐
FROM #t GROUP BY 工号,姓名
