人员表 a01 a0191='在职人员'
工资表 c21
部门表 deptcode
解除合同登记信息表 stopcontract
a01 与c21 关联字段a0188,
a01与stopcontract关联字段 a0188
c21与deptcode关联字段 dept_code怎么做下面的报表
部门 上月人数 增减 本月人数 应发金额(元) 其他扣款 个人所得税 扣款合计 实发金额
主要是求出上面的上月人数,以及本月人数,增减人数..
工资表 c21
部门表 deptcode
解除合同登记信息表 stopcontract
a01 与c21 关联字段a0188,
a01与stopcontract关联字段 a0188
c21与deptcode关联字段 dept_code怎么做下面的报表
部门 上月人数 增减 本月人数 应发金额(元) 其他扣款 个人所得税 扣款合计 实发金额
主要是求出上面的上月人数,以及本月人数,增减人数..
deptcode.dept_code,
deptcode.content,
sum(c2106) as '应发金额',
sum(c2158) as '其他扣款',
sum(c214r) as '扣社保',
sum(c214q) as '补扣社保',
sum(c214s) as '扣公积金',
sum(c214v) as '手机超标',
sum(c214w) as '会费',
sum(c2122) as '个人所得税',
sum(c214d) as '扣款合计',
sum(sfgz) as '实发金额',
a.c as '上月人数',
(m.d-a.c) as '增减',
m.d as '本月人数'
from c21,deptcode,a01,
(select count(a01.a0188)c,dept_code from a01,c02,deptcode
where a01.a0188=c02.a0188 and a01.dept_id=deptcode.dept_id
and
((dbo.ym(a0144)<'201007' and a0191='在职人员')
or (dbo.ym(c0201)>= '201007' and a0191='离职人员' and dbo.ym(a0144)<'201007'))
and a0193=0
group by deptcode.dept_code)a,(select count(a01.a0188)d,dept_code from a01,c02,deptcode
where a01.a0188=c02.a0188 and a01.dept_id=deptcode.dept_id
and
((dbo.ym(a0144)<=v and a0191='在职人员')
or (dbo.ym(c0201)>='201007' and a0191='离职人员' and dbo.ym(a0144)<='201007' ))
and a0193=0
group by deptcode.dept_code)m
where c21.a0188=a01.a0188
and c21.dept_id=deptcode.dept_id
and c21.gz_ym='201007'
and deptcode.dept_code=a.dept_code
and deptcode.dept_code=m.dept_code
and deptcode.dept_code like '103' +'%'
group by a01.a0159,deptcode.content,deptcode.dept_code,a.c,m.d