create table #test(emp_id nvarchar(100),att_dt datetime,att_type nvarchar(100),att_value float)
insert into #test
select '1','2011-4-4','迟到',10 union all
select '1','2011-4-4','休息迟到',20 union all
select '1','2011-4-4','早退',7 union all
select '1','2011-4-4','休息早退',4 union allselect '2','2011-4-4','迟到',0 union all
select '2','2011-4-4','休息迟到',10 union all
select '2','2011-4-4','早退',5 union all
select '2','2011-4-4','休息早退',4 union allselect '3','2011-4-4','迟到',43 union all
select '3','2011-4-4','休息迟到',0 union all
select '3','2011-4-4','早退',17 union all
select '3','2011-4-4','休息早退',14 union allselect '4','2011-4-4','迟到',15 union all
select '4','2011-4-4','休息迟到',40 union all
select '4','2011-4-4','早退',0 union all
select '4','2011-4-4','休息早退',43
create table #test1(id nvarchar(100),att_dt datetime,Be_late_value float,Leave_early_value float)
insert into #test1
select '1','2011-4-4',0,0 union all
select '2','2011-4-4',0,0 union all
select '3','2011-4-4',0,0 union all
select '4','2011-4-4',0,0 --根据#test更新#test1得到结果 --1 2011-4-4 30 9
--2 2011-4-4 10 9
--3 2011-4-4 43 31
--4 2011-4-4 55 43--根据#test更新#test1 ID和att_dt关联..
--#test1.Be_late_value=#test中同一个ID的同一天 迟到和休息迟到 求和
--#test1.Leave_early_value=#test中同一个ID的同一天 早退和休息早退 求和
b
set
Be_late_value=a.Be_late_value ,Leave_early_value =a.Leave_early_value
from
(
select
emp_id,
sum(case when att_type in('迟到','休息迟到') then 1 else 0 end) as Be_late_value,
sum(case when att_type in('早退','休息早退') then 1 else 0 end) as Leave_early_value
from
#test
group by
emp_id
)a
join
#test1 b
on
a.id=b.emp_id
create table #test(emp_id nvarchar(100),att_dt datetime,att_type nvarchar(100),att_value float)
insert into #test
select '1','2011-4-4','迟到',10 union all
select '1','2011-4-4','休息迟到',20 union all
select '1','2011-4-4','早退',7 union all
select '1','2011-4-4','休息早退',4 union allselect '2','2011-4-4','迟到',0 union all
select '2','2011-4-4','休息迟到',10 union all
select '2','2011-4-4','早退',5 union all
select '2','2011-4-4','休息早退',4 union allselect '3','2011-4-4','迟到',43 union all
select '3','2011-4-4','休息迟到',0 union all
select '3','2011-4-4','早退',17 union all
select '3','2011-4-4','休息早退',14 union allselect '4','2011-4-4','迟到',15 union all
select '4','2011-4-4','休息迟到',40 union all
select '4','2011-4-4','早退',0 union all
select '4','2011-4-4','休息早退',43
create table #test1(id nvarchar(100),att_dt datetime,Be_late_value float,Leave_early_value float)
insert into #test1
select '1','2011-4-4',0,0 union all
select '2','2011-4-4',0,0 union all
select '3','2011-4-4',0,0 union all
select '4','2011-4-4',0,0 update
b
set
Be_late_value=a.Be_late_value ,Leave_early_value =a.Leave_early_value
from
(
select
emp_id,
sum(case when att_type in('迟到','休息迟到') then att_value else 0 end) as Be_late_value,
sum(case when att_type in('早退','休息早退') then att_value else 0 end) as Leave_early_value
from
#test
group by
emp_id
)a
join
#test1 b
on
a.emp_id=b.id
select * from #test1
drop table #test,#test1
/*id att_dt Be_late_value Leave_early_value
---------------------------------------------------------------------------------------------------- ----------------------- ---------------------- ----------------------
1 2011-04-04 00:00:00.000 30 11
2 2011-04-04 00:00:00.000 10 9
3 2011-04-04 00:00:00.000 43 31
4 2011-04-04 00:00:00.000 55 43(4 行受影响)
*/
insert into #test
select '1','2011-4-4','迟到',10 union all
select '1','2011-4-4','休息迟到',20 union all
select '1','2011-4-4','早退',7 union all
select '1','2011-4-4','休息早退',4 union allselect '2','2011-4-4','迟到',0 union all
select '2','2011-4-4','休息迟到',10 union all
select '2','2011-4-4','早退',5 union all
select '2','2011-4-4','休息早退',4 union allselect '3','2011-4-4','迟到',43 union all
select '3','2011-4-4','休息迟到',0 union all
select '3','2011-4-4','早退',17 union all
select '3','2011-4-4','休息早退',14 union allselect '4','2011-4-4','迟到',15 union all
select '4','2011-4-4','休息迟到',40 union all
select '4','2011-4-4','早退',0 union all
select '4','2011-4-4','休息早退',43 create table #test1(id nvarchar(100),att_dt datetime,Be_late_value float,Leave_early_value float)
insert into #test1
select '1','2011-4-4',0,0 union all
select '2','2011-4-4',0,0 union all
select '3','2011-4-4',0,0 union all
select '4','2011-4-4',0,0 go
update a set Be_late_value=b.Be_late_value,Leave_early_value=b.Leave_early_value from #test1 a inner join(
select emp_id,att_dt,
Be_late_value=sum(case when att_type like '%迟到' then att_value else 0 end),
Leave_early_value=sum(case when att_type like '%早退' then att_value else 0 end)
from #test group by emp_id,att_dt
)b on b.emp_id=a.id and b.att_dt=a.att_dt
select * from #test1
/*
id att_dt Be_late_value Leave_early_value
---------------------------------------------------------------------------------------------------- ----------------------- ---------------------- ----------------------
1 2011-04-04 00:00:00.000 30 11
2 2011-04-04 00:00:00.000 10 9
3 2011-04-04 00:00:00.000 43 31
4 2011-04-04 00:00:00.000 55 43(4 行受影响)*/
go
drop table #test,#test1