How to get the employee details as on different effective dates?
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