| Summary Scenario [message #18477] |
Fri, 01 February 2002 02:49  |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
This is the scenario:
The procedure below runs on a daily basis. What l would like to do is to
At the beginning of each month a stored precedure needs to run that will
summarize all the data older than 3months,store it in a SummaryTable2 which has the same structure as SummaryTable1 and
delete it from The SummaryTable1.Eg at the beginning of April all
december's data needs to be summarized(by Branch,subbroker,broker etc)
from Decemeber and removed from the Summary Table.(The date in the month
summary table should be set to a date in December eg. 2001-12-01)
Procedure Mis_WorkFlow_pdincl
(v_start_date DATE,v_end_date DATE)
AS
BEGIN
insert into MIS_WRKFLW_Pdincl(branch,sbrokercd,Received,rec_count,summary_date,change_date)
select branch,sbrokercd, sum(pdincl),count(pdincl),sysdate,v_start_date
from ZW30800P
where SCDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Pdincl(branch,sbrokercd,Prequota,prequota_count,summary_date,change_date)
select branch,sbrokercd,sum(pdincl),count(pdincl),sysdate,v_start_date
from ZW30800P
where STATUS = 'PRE'
and PQDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd);
insert into MIS_WRKFLW_Pdincl(branch,sbrokercd,rejected,rec_count,summary_date,change_date)
select branch,sbrokercd,sum(pdincl),count(pdincl),sysdate,v_start_date
from ZW30800P
where STATUS = 'REJ'
and REJDATE between v_start_date and v_end_date
group by rollup(branch,sbrokercd)
etc .....
;
end;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
