How to get the employee details as on different effective dates?

Question

Please see the following images. Its essentially how my database looks like.

BI_EMPLOYEE table always has the current record as on today. Though it may be effective from some previous date.

e.g. Employee Anna as shown in image has joined from 31st march and till today there are no updates made to that employee.

Hence, BI_EMPLOYEE has effective date = 31 march 2012.

But, later on there may be movements to employee on future dates which is know now.

e.g. anna is moving from asia to us on 10 May and so on. So, effective from 10 may her region will be us.

So, I need a report which will give me list of all employees with their status as on every effective dates. Basically, all emplyees all activities and updates on various dates.

We also runa schedular every day which run all the updates and activities i.e. making chnages to the BI_EMPLOYEE TABLE from the update and the activities table.

So, as on 10 May schedular will change region from asia to us .

I have also atached an image with the expected result.

We are using Oracle database.

Please help.

I will basically need to create a view which will give the desired output which then can be queried any time for any report.

EDIT 1:

Below are the DDL's and DML's

        --------------------------------------------------------
    --  DDL for Table BI_REGION
    --------------------------------------------------------
     CREATE TABLE "BI_REGION" 
       (    "REGION_ID" NUMBER(*,0) NOT NULL ENABLE, 
        "REGION_NAME" VARCHAR2(4000) NOT NULL ENABLE
       ) ;
    --------------------------------------------------------
    --   DATA FOR TABLE BI_REGION
    --------------------------------------------------------
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (1,'Asia');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (2,'US');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (3,'UK');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (4,'Germany');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (5,'EUROPE');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (6,'AUSTRALIA');

    -------------------------------------------------------
    --  DDL for Table BI_COUNTRY
    --------------------------------------------------------
      CREATE TABLE "BI_COUNTRY" 
       (    "COUNTRY_ID" NUMBER(*,0) NOT NULL ENABLE, 
        "COUNTRY_NAME" VARCHAR2(4000) NOT NULL ENABLE 
       ) ;

    --   DATA FOR TABLE BI_COUNTRY
    --   FILTER = none used
    ---------------------------------------------------
    REM INSERTING into BI_COUNTRY
    Insert into BI_COUNTRY (COUNTRY_ID,COUNTRY_NAME) values (1,'India');
    Insert into BI_COUNTRY (COUNTRY_ID,COUNTRY_NAME) values (2,'USA');
    Insert into BI_COUNTRY (COUNTRY_ID,COUNTRY_NAME) values (3,'England');
    Insert into BI_COUNTRY (COUNTRY_ID,COUNTRY_NAME) values (4,'Germany');
    Insert into BI_COUNTRY (COUNTRY_ID,COUNTRY_NAME) values (5,'New Zealnd');

    --------------------------------------------------------
    --  DDL for Table BI_DIVISION
    --------------------------------------------------------
      CREATE TABLE "BI_DIVISION" 
       (    "DIVISION_ID" NUMBER(*,0) NOT NULL ENABLE, 
        "DIVISION_NAME" VARCHAR2(4000) 
       ) ;

    ---------------------------------------------------
    --   DATA FOR TABLE BI_DIVISION
    --   FILTER = none used
    ---------------------------------------------------

    Insert into BI_DIVISION (DIVISION_ID,DIVISION_NAME) values (1,'D1');
    Insert into BI_DIVISION (DIVISION_ID,DIVISION_NAME) values (2,'D2');


    --------------------------------------------------------
    --  DDL for Table BI_PRODUCT
    --------------------------------------------------------
      CREATE TABLE "BI_PRODUCT" 
       (    "PRODUCT_NAME" VARCHAR2(4000), 
        "PRODUCT_ID" NUMBER(*,0) NOT NULL ENABLE 
       ) ;

    ---------------------------------------------------
    --   DATA FOR TABLE BI_PRODUCT
    --   FILTER = none used
    ---------------------------------------------------
    REM INSERTING into BI_PRODUCT
    Insert into BI_PRODUCT (PRODUCT_NAME,PRODUCT_ID) values ('P1','1');
    Insert into BI_PRODUCT (PRODUCT_NAME,PRODUCT_ID) values ('P2','2');


    --------------------------------------------------------
    --  DDL for Table BI_EMPLOYEE_ACTIVITY
    --------------------------------------------------------
      CREATE TABLE "BI_EMPLOYEE_ACTIVITY" 
       (    "ACTIVITY_ID" NUMBER(*,0) NOT NULL ENABLE, 
        "STATUS" NUMBER(*,0), 
        "STATUS_2" NUMBER(*,0),     
        "STATUS_3" VARCHAR2(4000), 
        "CONFIDENTIAL" VARCHAR2(1 CHAR) DEFAULT 'N', 
        "EFFECTIVE_DATE" DATE, 
        "PARENT_ACTIVITY_ID" NUMBER(*,0), 
        "EXECUTED" VARCHAR2(1 CHAR) DEFAULT 'N', 
        "EMPLOYEE_ID" NUMBER 
       ) ;



    ---------------------------------------------------
    --   DATA FOR TABLE BI_EMPLOYEE_ACTIVITY
    --   FILTER = none used
    ---------------------------------------------------

    REM INSERTING into BI_EMPLOYEE_ACTIVITY
    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (1,1,'Leaver','Intra Region','','No','10-May-2012',0);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (2,1,'Joiner','Intra Region','','No','10-May-2012',1);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (3,1,'Leaver','Intra Region','','No','10-June-2012',0);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (4,1, 'Joiner','Intra Region','','No','10-June-2012',3);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (5,1,'Leaver','Intra Region','','No','10-July-2012',0);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (6,1, 'Joiner','Intra Region','','No','10-July-2012',5);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (7,1,'Leaver','Intra Business','','No','10-Aug-2012',0);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (8,1, 'Joiner','Intra Business','','No','10-Aug-2012',7);

    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (9,1,'Leaver','Intra Business','','No','10-Sep-2012',0);


    Insert into BI_EMPLOYEE_ACTIVITY (ACTIVITY_ID,EMPLOYEE_ID, STATUS,STATUS_2,STATUS_3,CONFIDENTIAL,EFFECTIVE_DATE,PARENT_ACTIVITY_ID,) 
    values (10,1, 'Joiner','Intra Business','','No','10-Sep-2012',9);




    --------------------------------------------------------
    --  DDL for Table BI_EMPLOYEE
    --------------------------------------------------------
      CREATE TABLE "BI_EMPLOYEE" 
       (    "EMP_ID" NUMBER(*,0) NOT NULL ENABLE, 
        "FIRSTNAME" VARCHAR2(4000), 
        "LASTNAME" VARCHAR2(4000), 
        "DIVISION_ID" NUMBER(*,0), 
        "PRODUCT_ID" NUMBER(*,0), 
        "REGION_ID" NUMBER(*,0), 
        "COUNTRY_ID" NUMBER(*,0), 
        "CITY" VARCHAR2(4000) ,
        "EFFECTIVE_DATE" DATE
       ) ;

    ---------------------------------------------------
    --   DATA FOR TABLE BI_EMPLOYEE
    --   FILTER = none used
    ---------------------------------------------------
    Insert into BI_EMPLOYEE (EMP_ID,FIRSTNAME,LASTNAME,DIVISION_ID,PRODUCT_ID,REGION_ID,COUNTRY_ID,EFFECTIVE_DATE,CITY) values (1,'Ana','Johnston',1,1,1,1,'31-March-2012','Mumbai');


    --------------------------------------------------------
    --  DDL for Table BI_EMPLOYEE_UPDATE
    --------------------------------------------------------
      CREATE TABLE "BI_EMPLOYEE_UPDATE" 
       (    "EMPLOYEE_UPDATE_ID" NUMBER(*,0) NOT NULL ENABLE, 
        "EMPLOYEE_ID" NUMBER(*,0), 
        "COLUMN_NAME" VARCHAR2(4000), 
        "OLD_VALUE" VARCHAR2(4000), 
        "NEW_VALUE" VARCHAR2(4000), 
        "EFFECTIVE_DATE" DATE, 
        "EXECUTED" VARCHAR2(1 CHAR) DEFAULT 'N', 
        "ACTIVITY_ID" NUMBER 
       ) ;


    ---------------------------------------------------
    --   DATA FOR TABLE BI_EMPLOYEE_UPDATE
    --   FILTER = none used
    ---------------------------------------------------
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'REGION_ID','1','2','10-May-2012', 'N', 1);
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'COUNTRY_ID','1','2','10-May-2012', 'N', 1);
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'CITY','Mumbai','New York','10-May-2012', 'N', 1);

    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'REGION_ID','2','3','10-June-2012', 'N', 3);
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'COUNTRY_ID','2','3','10-June-2012', 'N', 3);
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'CITY','New York','London','10-June-2012', 'N', 3);

    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'REGION_ID','3','4','10-July-2012', 'N', 5);
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'COUNTRY_ID','3','4','10-July-2012', 'N', 5);
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'CITY','London','frankfurt','10-July-2012', 'N', 5);

    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'DIVISION_ID','1','2','10-Aug-2012', 'N', 7);
    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'Product_ID','1','2','10-Aug-2012', 'N', 7);

    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'PRODUCT_ID','2','3','10-Sep-2012', 'N', 9);


    Insert into BI_EMPLOYEE_UPDATE (EMPLOYEE_ID,COLUMN_NAME,OLD_VALUE,NEW_VALUE,EFFECTIVE_DATE,EXECUTED,ACTIVITY_ID)  values (1,'LASTNAME','Johnston','thomas','10-Nov-2012', 'N', 0);

Okay

Answer 1

First, a better data structure is to have an effective date and end date in the data. This way, you can see which record is active on any given date.

Fortunately, you can get the end date in Oracle with the following query:

select eu.*,
       (lead(effective_date, 1, '9999-01-01') over (partition by employee_id, column_name
                                      order by effective_date)) - 1) as end_date
from bi_employee_update

This query looks at the next effective date, using the "lead" analytic function. It then subtracts one day to get the end date for the current record, with a default value of 01Jan9999 for the current record.

Using this query, you can get information about an emmployee on any date by using a between clause:

select eu.*
from (select eu.*,
             (lead(effective_date, 1, '9999-01-01') over (partition by employee_id, column_name
                                      order by effective_date)) - 1) as end_date
      from bi_employee_update
     ) eu
where <date> between effective_date and end_date

Can you take the query from here?