Hi All,
I wonder if I can get some help on a query that I need to write to extract some information from the database. I am using Oracle 12c.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
I have a main table called eg. MAIN_TABLE where parts (denoted by part_no) are maintained with a unique part_no eg A1_001, A1_002 etc and each has an entry in the table where its 'usage' has accumulated over time. The usage being 'HOURS' * OLD_RATE .
eg. A1_001 has usage of 500, due to 50 (hours) * 10 (old_rate) = 500.
Each of these rows has a uniqe id ie UNIQUE_ID which can be used to update the table when required later.
There is a requirement to be able to amend the 'Usage' field within Main_table. The forms application that its written from now needs to allow usage to be amended over a date range. Whereas main_table didnt have a date field to be used and only accumulated and updated the usage figure when say 100 needed to be added to the usage column it would change from 400 to 500.
There will now be a transaction_log table created where we will introduce dates so that a front end Forms user can input a FROM_DATE and a TO_DATE and a new rate that they want applying for a particular part_no they select and thus the system will go away and calculate the new usage and update it in the main_table using this transaction_log table as a reference.
So every time a usage update is performed in the system the transaction_log captures this information and date stamps it with the usages pre_usage and post_usage in the transaction_log along with the uniqe_id to be used to update the main_table.
If the user wishes to change the usage within a date range ie FROM DATE : 20-JAN-2021 , TO_DATE: 29-JAN-2021 from OLD_RATE : 10 to NEW_RATE: 15 for part_no : A1_001 he would need to find the pre_usage (or post_usage if I wanted end of day on from_date and to_date) closest to the dates matching in the transaction_log ie with a from_date of 20-jan-2021 the closest usage will be 350 (pre_usage) to that date stamp, and a to_date of 29-jan-2021 the usage will be 400 as theres none on those exact dates so need to use dates closest to from_date and to_date via the transaction_date in transaction_log.
Then having the usage figures for from_date and to_date I would then use those figures and do some pl/sql such as the following to apply the NEW_RATE between that time period and ultimately update the usage with the calculated figure in the MAIN_TABLE
usage\_part1 := USAGE (CLOSEST TO TO\_DATE) - USAGE(CLOSEST TO FROM\_DATE) ;
usage\_part2 := NEW\_RATE - OLD\_RATE;
usage\_new := ( usage\_part1 \* usage\_part2) + usage (FROM MAIN\_TABLE);
So the query I would need to write is taking the from and to_date from the user (assume for now from_date = 20-JAN-2021 and to_date = 29-JAN-2021 for part_no : A1_001, rather than having any parameters as input in sql) and searching the transaction_log for that part_no with the usages returned for the dates closest to those dates in the transaction_LOG (using TRANSACTION_DATE for from_date and to_date) which I can then use to update the usage in main_table via a unique_id after manipulating the figures to take account of the new_rate. The usage in the main table will obviously now not be a straightforward usage = hours * old_rate any more as the usage value has been changed and manipulated.
The whole idea behind this to be be able to update the usage value in main_table using a date_range with a new_rate rather than how it is now where theres no dates in the main_table to apply changes to cover a date period
Thanks in advance for looking
Regards..
I have created some sample date here ...
CREATE TABLE MAIN_TABLE(
PART_NO VARCHAR2(16 BYTE) NOT NULL,
USAGE NUMBER(11,3),
HOURS NUMBER(11,3),
OLD_RATE NUMBER(11,3),
UNIQUE_ID NUMBER
);
INSERT INTO MAIN_TABLE VALUES('A1_001',500, 50, 10, 10001);
INSERT INTO MAIN_TABLE VALUES('A1_002',540, 45, 12, 10002);
INSERT INTO MAIN_TABLE VALUES('A1_003',1150, 100, 11.5, 10003);
INSERT INTO MAIN_TABLE VALUES('A1_004',360, 45, 8, 10004);
INSERT INTO MAIN_TABLE VALUES('A1_005',350, 35, 10, 10005);
CREATE TABLE TRANSACTION_LOG (
PART_NO VARCHAR2(16 BYTE) NOT NULL,
TRANSACTION_ID NUMBER NOT NULL,
TRANSACTION_DATE DATE NOT NULL,
PRE_USAGE NUMBER(11,3),
POST_USAGE NUMBER(11,3),
USAGE_APPLIED NUMBER(11,3),
UNIQUE_ID NUMBER
);
INSERT INTO TRANSACTION_LOG VALUES('A1_001',1,'16-JAN-2021', 300, 350, 50, 10001);
INSERT INTO TRANSACTION_LOG VALUES('A1_001',2,'23-JAN-2021', 350, 370, 20, 10001);
INSERT INTO TRANSACTION_LOG VALUES('A1_001',3,'24-JAN-2021', 370, 380, 10, 10001);
INSERT INTO TRANSACTION_LOG VALUES('A1_001',4,'26-JAN-2021', 380, 400, 20, 10001);
INSERT INTO TRANSACTION_LOG VALUES('A1_001',5,'01-FEB-2021', 400, 425, 25, 10001);
INSERT INTO TRANSACTION_LOG VALUES('A1_001',6,'09-FEB-2021', 425, 500, 75, 10001);
INSERT INTO TRANSACTION_LOG VALUES('A1_002',7,'16-JAN-2021', 45, 69, 24, 10002);
INSERT INTO TRANSACTION_LOG VALUES('A1_002',8,'23-JAN-2021', 69, 81, 12, 10002);
INSERT INTO TRANSACTION_LOG VALUES('A1_002',9,'24-JAN-2021', 81, 117, 36, 10002);
commit;