Calculate the SUM of multiple rows from one table and have it written to another table, weekly

I am not an experienced SQL coder and sort of learn as I go. I have a task that needs to be executed automatically on a weekly basis. There's a few parts to this job:

1) Calculate the SUM of multiple rows from a couple of columns.

select SUM(TOTAL_COST) from TABLE_1 and select SUM(TOTAL_HOURS) from TABLE_1

2) Write the 2 SUMs from TABLE_1 to 2 columns in TABLE_2.

Let's call these columns: SUM_OF_COST and SUM_OF_HOURS from Table_2

3) This job needs to be executed weekly.

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
  name         =>  'update_job',
   attribute    =>  'repeat_interval',
   value        =>  'freq=weekly; byday=wed');
END;
/

The codes above are just from my understanding of I THINK what would be used. I know I have to write the entire job from the first 2 steps above into a package? I'm assuming I would reference that package in the job (step 3). I need help with how I would go about writing this. Does the SUMs need to be written to another column before they can be written to TABLE_2 or can it calculate the SUM and then write the value to TABLE_2 without storing that value in TABLE_1? How do I piece together the code to do the calculations and then write to the second table? Any help would be greatly, greatly, appreciated. Thank you!

Answers


You are in right way.

You need to create a procedure that will be performed every week.

CREATE OR REPLACE PROCEDURE sum_weekly IS
BEGIN
 INSERT INTO TABLE_2(SUM_OF_COST ,SUM_OF_HOURS ) (SELECT SUM(cost),SUM(hour) FROM TABLE_1)
 COMMIT;
END;

More about insert select syntax.

Then you need create schedule:

BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
  job_name             => 'my.sum_weekly.schedule',
  job_type             => 'PLSQL_BLOCK',
  job_action           => 'BEGIN sum_weekly(); END;',
  start_date           =>  sysdate,
  repeat_interval      => 'FREQ=WEEKLY', 
  enabled              =>  TRUE,
  comments             => 'Gather table statistics from Table 1 to Table 2');
END;

Examples for repeat_interval.

FREQ=HOURLY;INTERVAL=4
every 4 hour
FREQ=HOURLY;INTERVAL=4;BYMINUTE=10;BYSECOND=30
every 4 hours, 10 minutes, 30 seconds of;
FREQ=YEARLY;BYYEARDAY=-276
every 31 th March;
FREQ=YEARLY;BYMONTH=MAR;BYMONTHDAY=31
every 31 th March;

To check correct set interval

DECLARE   next_run_date   TIMESTAMP;
BEGIN
 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
 'FREQ=HOURLY;INTERVAL=4;BYMINUTE=10;BYSECOND=30'
 ,SYSTIMESTAMP
 ,NULL
 ,next_run_date) ;
DBMS_OUTPUT.PUT_LINE ( 'next_run_date: ' || next_run_date );
END;

To see your schedules use following query:

SELECT job_name, state, enabled FROM user_scheduler_jobs;

Need Your Help

Chainer: Cannot deserialize

python chainer

Using chainer, I have model learn ptb text. Then, serialized model and optimizer. However, I cannot deserialize them.

Sharing data between 2 usercontrols in WPF

c# wpf

I have usercontrol lets say UA with a textbox in it. Now i want to access the value in the textbox in the codebehind of another usercontrol, let says UB