结算工资问题

来源：互联网发布：电信上网软件编辑：程序博客网时间：2024/05/10 17:12

--结算工资问题
--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