有如下几个表:
部门表:t_department
id,name
1,部门一
2,部门二
3,部门三
4,部门四投资表:t_invest
主键,部门编号,投资金额,投资时间
id,dept_id,money,invest_time
配额表:t_quota
主键,部门编号,配额金额,配额时间
id,dept_id,quota,quota_time
实现如下报表表头的日期是两个参数传递进来的,一个开始日期,一个结束日期,周一到周日计算为一周,然后根据开始日期和结束日期统计,每周汇总一次,每月汇总一次。投资金额计算:往前找无配额设置日期的投资金额累加一直到有配额日期为止各位高人帮帮忙,高分相送!!!有不明白的地方我再补充!这里只能发100分,如果可以的话我给各位再送200分!!
部门表:t_department
id,name
1,部门一
2,部门二
3,部门三
4,部门四投资表:t_invest
主键,部门编号,投资金额,投资时间
id,dept_id,money,invest_time
配额表:t_quota
主键,部门编号,配额金额,配额时间
id,dept_id,quota,quota_time
实现如下报表表头的日期是两个参数传递进来的,一个开始日期,一个结束日期,周一到周日计算为一周,然后根据开始日期和结束日期统计,每周汇总一次,每月汇总一次。投资金额计算:往前找无配额设置日期的投资金额累加一直到有配额日期为止各位高人帮帮忙,高分相送!!!有不明白的地方我再补充!这里只能发100分,如果可以的话我给各位再送200分!!
http://hiphotos.baidu.com/ivencheung/pic/item/54de757b744595af0ad18791.jpg
prompt Created on 2011年5月23日星期一 by Administrator
set feedback off
set define off
prompt Creating T_DEPARTMENT...
create table T_DEPARTMENT
(
DEPARTMENT_ID NUMBER not null,
DEPARTMENT_NAME VARCHAR2(100)
)
;prompt Creating T_INVEST...
create table T_INVEST
(
ID NUMBER,
DEPT_ID NUMBER,
MONEY NUMBER,
INVEST_TIME VARCHAR2(20)
)
;prompt Creating T_QUOTA...
create table T_QUOTA
(
ID NUMBER,
DEPT_ID NUMBER,
QUOTA NUMBER,
QUOTA_TIME VARCHAR2(20)
)
;prompt Loading T_DEPARTMENT...
insert into T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME)
values (612, '部门一');
insert into T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME)
values (691, '部门二');
insert into T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME)
values (798, '部门三');
insert into T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME)
values (799, '部门四');
commit;
prompt 4 records loaded
prompt Loading T_INVEST...
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (1, 612, 600, '2011-05-01');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (2, 612, 200, '2011-05-02');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (3, 612, 300, '2011-05-03');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (4, 612, 1000, '2011-05-04');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (5, 691, 150, '2011-05-05');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (6, 691, 220, '2011-05-06');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (7, 691, 330, '2011-05-07');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (8, 691, 800, '2011-05-08');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (9, 691, 600, '2011-05-09');
insert into T_INVEST (ID, DEPT_ID, MONEY, INVEST_TIME)
values (10, 799, 800, '2011-05-10');
commit;
prompt 10 records loaded
prompt Loading T_QUOTA...
insert into T_QUOTA (ID, DEPT_ID, QUOTA, QUOTA_TIME)
values (2, 612, 1000, '2011-05-02');
insert into T_QUOTA (ID, DEPT_ID, QUOTA, QUOTA_TIME)
values (3, 612, 500, '2011-05-03');
insert into T_QUOTA (ID, DEPT_ID, QUOTA, QUOTA_TIME)
values (4, 612, 2000, '2011-05-04');
insert into T_QUOTA (ID, DEPT_ID, QUOTA, QUOTA_TIME)
values (5, 691, 300, '2011-05-05');
insert into T_QUOTA (ID, DEPT_ID, QUOTA, QUOTA_TIME)
values (6, 691, 800, '2011-05-06');
insert into T_QUOTA (ID, DEPT_ID, QUOTA, QUOTA_TIME)
values (9, 691, 1200, '2011-05-09');
insert into T_QUOTA (ID, DEPT_ID, QUOTA, QUOTA_TIME)
values (10, 799, 1000, '2011-05-10');
commit;
prompt 7 records loaded
set feedback on
set define on
prompt Done.