Home » SQL & PL/SQL » SQL & PL/SQL » Summary Scenario
Summary Scenario [message #18477] Fri, 01 February 2002 02:49 Go to next message
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;
Re: Summary Scenario [message #18479 is a reply to message #18477] Fri, 01 February 2002 03:55 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
It can't be done. No matter how much time or how many people. Tell your boss s/he is asking the impossible.
Previous Topic: PLS-00103: Encountered !!!!!!!!!!
Next Topic: Code Sucks
Goto Forum:
  


Current Time: Tue Apr 07 03:07:10 CDT 2020