0

I have a table named booking. The primary key is booking_id. The table has a column named booking_num. booking_num is used to identify the booking.

Another table is booking_hist. The purpose is to transfer completed records of booking from booking table into booking_hist table after a certain period, let's say 2 days. The column to identify it will be completed_dt. For example, completed_dt is '08-SEP-19'.

What I'm trying to achieve is that after immediately 2 days after this date, it will be moved into booking_hist table.

Should there be any null<->non-null conversions of the column? What is the logic I need to achieve this? How can i get the date to count 2 days?

Z.H
  • 25
  • 1
  • 4
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Sep 18 '19 at 15:41

3 Answers3

0

You can schedule a SQL Agent job runs daily and call a stored procedure to go through the active bookings and check the completed_dt like:

-- add your insert here, e.g. INSERT INTO bookings_hist (...)
SELECT *
FROM booking b
LEFT JOIN booking_hist h
ON b.booking_id=h.booking_id
WHERE h.booking_id IS NULL
AND completed_dt IS NOT NULL
AND completed_dt<DATEADD(DAY,-2,GETDATE());
PeterHe
  • 2,766
  • 1
  • 8
  • 7
0

Create a stored procedure to move from one table to other

how to call the stored procedure in oracle with the daily scheduled jobs?

In where condition add , trunc(sysdate) - to_date('completed_dt', 'yyyy-mm-dd') >2

Please refer below link for other options:

How can I get the number of days between 2 dates in Oracle 11g?

Ast
  • 143
  • 7
0

This sounds like the kind of thing that should happen in a scheduled job. I would add a ready_for_archive column to booking-just a boolean flag.

I would have a query that marked all bookings that happen before a specified date/time, and pass in the date of 2 days ago from java. Something like

 UPDATE booking
 SET ready_for_archive = 1
 WHERE completed_dt <= :MY_START_DATE

Then I would add all of these records to the historical table

 INSERT INTO booking_hist
 SELECT * FROM booking
 WHERE ready_for_archive = 1;

Then remove them from the booking table:

 DELETE FROM booking
 WHERE ready_for_archive = 1;

Marking the records to be archived before doing that process means there's no risk of accidentally deleting records that were one second too young to be copied.

Passing in the date after calculating it in Java makes the sql query more generic and reusable.

Chris Belyeu
  • 208
  • 1
  • 9