SQL & PL/SQL

Hi All,

I have a table lets say Employee and 2nd table EMPLOYEE_TERM

Employee table stores a Surrogate_KEY, employee_id , employee_name , designation , place_of_deployment,scd_start_date , scd_end_date , status

EMPLOYEE_TERM stores EMPLOYEE_ID and TERM_DATE.

Requirement:

I need to find the record after termination_date. Because a Employee could be active, passive ,on leave, contraul, etc. so we need to find exactly after termination what was the status

Prepared below code in excel as I do not have Oracle installed on my local system

Kindly help

-- Create employee table (SCD Type 2)
CREATE TABLE employee (
    surrogate_key        NUMBER PRIMARY KEY,    -- Unique surrogate key for each change
    employee_id          NUMBER,                 -- Employee ID
    employee_name        VARCHAR2(100),          -- Employee Name
    designation          VARCHAR2(50),           -- Employee Designation
    place_of_deployment  VARCHAR2(100),          -- Place of deployment
    scd_start_date       DATE,                    -- Start date of the current record
    scd_end_date         DATE,                    -- End date of the current record
    status               VARCHAR2(20)            -- Employee status (active, passive, terminated)
);

-- Create employee_term table (stores employee termination date)
CREATE TABLE employee_term (
    employee_id      NUMBER,          -- Employee ID
    termination_date DATE             -- Termination date
);

INSERT INTO employee VALUES (1, 101, 'John Smith',   'Analyst',  'Delhi',     DATE '2022-01-01', DATE '2022-12-31', 'active');
INSERT INTO employee VALUES (2, 101, 'John Smith',   'Sr Analyst','Delhi',    DATE '2023-01-01', DATE '2023-10-15', 'passive');
INSERT INTO employee VALUES (3, 101, 'John Smith',   'Manager',  'Mumbai',    DATE '2023-10-16', DATE '9999-12-31', 'active');

INSERT INTO employee VALUES (4, 102, 'Priya Patel',  'Consultant','Pune',     DATE '2021-06-01', DATE '2023-03-31', 'active');
INSERT INTO employee VALUES (5, 102, 'Priya Patel',  'Lead',     'Chennai',   DATE '2023-04-01', DATE '2024-06-30', 'active');
INSERT INTO employee VALUES (6, 102, 'Priya Patel',  'Lead',     'Hyderabad', DATE '2024-07-01', DATE '9999-12-31', 'active');

INSERT INTO employee VALUES (7, 103, 'Amit Verma',   'Engineer', 'Noida',     DATE '2022-05-01', DATE '2023-07-31', 'active');

-- Insert sample records into employee_term table (Termination dates)
INSERT INTO employee_term VALUES (101, DATE '2023-10-15');
INSERT INTO employee_term VALUES (102, DATE '2024-06-30');
INSERT INTO employee_term VALUES (103, DATE '2023-08-01');  -- No record after this termination date