22,206
社区成员
发帖
与我相关
我的任务
分享
--求每个员工每个月的同期累计和当期累计
create table months(m varchar(7))
insert into months select '2007-01' unoin all select '2007-02' union all select '2007-03' union all --......一直到'2008-12'
go
select a.m,c.员工id,
isnull(b.工资,0) as 同期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<=a.m and 月份>='2007-01' and 员工id=c.员工id) as 同期累计,
(select isnull(工资,0) from 员工工资表 where 月份='2008-' + right(a.m,2) and 员工id=c.员工id) as 当期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<='2008-' + right(a.m,2) and 月份>='2008-01' and 员工id=c.员工id) as 当期累计,
from months a left join 员工工资表 on a.m=b.月份 right join 员工档案表 c on b.员工id=c.员工id
where a.m like '2007-%' and b.月份
--求每个员工每个月的同期累计和当期累计
create table months(m varchar(7))
insert into months select '2007-01' unoin all select '2007-02' union all select '2007-03' union all --......一直到'2008-12'
go
select c.员工id,a.m as 月份,
isnull(b.工资,0) as 同期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<=a.m and 月份>='2007-01' and 员工id=c.员工id) as 同期累计,
(select isnull(工资,0) from 员工工资表 where 月份='2008-' + right(a.m,2) and 员工id=c.员工id) as 当期,
(select sum(isnull(b.工资),0) from 员工工资表 where 月份<='2008-' + right(a.m,2) and 月份>='2008-01' and 员工id=c.员工id) as 当期累计,
from months a left join 员工工资表 on a.m=b.月份 right join 员工档案表 c on b.员工id=c.员工id
where a.m like '2007-%' and b.月份