--结算工资问题
--t_online 表 员工的生命周期表 员工每次调动,注册,注销 都会存一条记录在里面
--t_running_mtcoststd 表 员工的工资 每次工资调整都会在里面记一条记录
--求 某一时间段内的 按部门汇总 求每个部门的工资总额
--时间段范围 是 2010/01/01 到2010/12/31
create table T_ONLINE
(
--结算工资问题
-- T_ONLINE表 员工的生命周期表 员工每次调动 都会存一条记录在里面
-- T_RUNNING_SALRY表 员工的工资 每次工资调整 都会在里面记一条记录
--求 某一时间段内的 按部门汇总 求每个部门的工资总额
--时间段范围 是 2010/01/01 到2010/12/31
create table T_ONLINE
(
C_BELONG NUMBER(18) not null, --所属机构
C_ONLINEPSNID NUMBER(18) not null, --员工ID
C_ONLINEDEPARTMENT NUMBER(18) not null, --部门ID
C_ONDATE DATE not null, --开始时间
C_DOWNDATE DATE not null --结束时间
);
create table T_RUNNING_SALRY
(
C_BELONG NUMBER(18) not null, --所属机构
C_PSNID NUMBER(18) not null, --员工ID
C_FROMDATE DATE not null, --工资计算开始日期
C_ENDDATE DATE not null, --工资计算结束日期
C_STANDARD NUMBER(8,2) --月工资(每日工资 需要按当前月份的天数 分摊)
);
--比如 01-02-2010, 01-03-2010, 4500.00
--2月份每天的工资是4500/28
--3月1号那一天的工资是4500/31
--数据T_ONLINE
insert into t_online (C_BELONG, C_ONLINEPSNID, C_ONLINEDEPARTMENT, C_ONDATE, C_DOWNDATE)
values (4, 1, 1, to_date('01-01-2010', 'dd-mm-yyyy'), to_date('15-01-2010', 'dd-mm-yyyy'));
insert into t_online (C_BELONG, C_ONLINEPSNID, C_ONLINEDEPARTMENT, C_ONDATE, C_DOWNDATE)
values (4, 1, 2, to_date('16-01-2010', 'dd-mm-yyyy'), to_date('15-02-2010', 'dd-mm-yyyy'));
insert into t_online (C_BELONG, C_ONLINEPSNID, C_ONLINEDEPARTMENT, C_ONDATE, C_DOWNDATE)
values (4, 1, 1, to_date('16-02-2010', 'dd-mm-yyyy'), to_date('01-03-2010', 'dd-mm-yyyy'));
insert into t_online (C_BELONG, C_ONLINEPSNID, C_ONLINEDEPARTMENT, C_ONDATE, C_DOWNDATE)
values (4, 1, 2, to_date('02-03-2010', 'dd-mm-yyyy'), to_date('31-03-2010', 'dd-mm-yyyy'));
insert into t_online (C_BELONG, C_ONLINEPSNID, C_ONLINEDEPARTMENT, C_ONDATE, C_DOWNDATE)
values (4, 1, 1, to_date('01-04-2010', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'));
--数据T_RUNNING_SALRY
insert into t_running_salry (C_BELONG, C_PSNID, C_FROMDATE, C_ENDDATE, C_STANDARD)
values (4, 1, to_date('01-01-2010', 'dd-mm-yyyy'), to_date('31-01-2010', 'dd-mm-yyyy'), 5000.00);
insert into t_running_salry (C_BELONG, C_PSNID, C_FROMDATE, C_ENDDATE, C_STANDARD)
values (4, 1, to_date('01-02-2010', 'dd-mm-yyyy'), to_date('01-03-2010', 'dd-mm-yyyy'), 4500.00);
insert into t_running_salry (C_BELONG, C_PSNID, C_FROMDATE, C_ENDDATE, C_STANDARD)
values (4, 1, to_date('02-03-2010', 'dd-mm-yyyy'), to_date('01-04-2010', 'dd-mm-yyyy'), 5000.00);
insert into t_running_salry (C_BELONG, C_PSNID, C_FROMDATE, C_ENDDATE, C_STANDARD)
values (4, 1, to_date('02-04-2010', 'dd-mm-yyyy'), to_date('31-12-9999', 'dd-mm-yyyy'), 6000.00);
THE New:
select t_all.c_onlinedepartment,
sum((t_all.c_stand / t_all.c_days) *
(t_all.c_max_date - t_all.c_min_date + 1)) c_cost
from (select distinct t_source.c_onlinedepartment,
t_source.c_onlinepsnid,
t_source.c_stand,
greatest(t_source.c_min_date,
add_months(trunc(t_source.c_min_date, 'mm'),
level - 1)) c_min_date,
least(t_source.c_max_date,
last_day(add_months(trunc(t_source.c_min_date,
'mm'),
level - 1))) c_max_date,
last_day(add_months(trunc(t_source.c_min_date, 'mm'),
level - 1)) -
add_months(trunc(t_source.c_min_date, 'mm'),
level - 1) + 1 c_days
from (select t_online.c_onlinedepartment c_onlinedepartment,
t_online.c_onlinepsnid c_onlinepsnid,
nvl(t_running_salry.c_standard, 0) c_stand,
least(t_online.c_downdate,
t_running_salry.c_enddate,
to_date('31/12/2010', 'dd/mm/yyyy')) c_max_date,
greatest(t_online.c_ondate,
t_running_salry.c_fromdate,
to_date('01/01/2010', 'dd/mm/yyyy')) c_min_date
from t_online, t_running_salry
where t_online.c_belong = t_running_salry.c_belong
and t_online.c_onlinepsnid = t_running_salry.c_psnid
and t_online.c_belong = 4
and least(t_online.c_downdate,
t_running_salry.c_enddate,
to_date('31/12/2010', 'dd/mm/yyyy')) >=
greatest(t_online.c_ondate,
t_running_salry.c_fromdate,
to_date('01/01/2010', 'dd/mm/yyyy'))
And (t_online.c_downdate >
to_date('01/01/2010', 'dd/mm/yyyy'))
And t_online.c_ondate <=
to_date('31/12/2010', 'dd/mm/yyyy')) t_source
connect by level <=
(12 * (to_number(to_char(t_source.c_max_date, 'yyyy')) -
to_number(to_char(t_source.c_min_date, 'yyyy'))) +
to_number(to_char(t_source.c_max_date, 'mm')) -
to_number(to_char(t_source.c_min_date, 'mm')) + 1)) t_all
group by t_all.c_onlinedepartment