Как получить данные о сотруднике на разные даты вступления в силу?

Пожалуйста, смотрите следующие изображения. По сути, так выглядит моя база данных.

Таблица BI_EMPLOYEE всегда имеет текущую запись на сегодняшний день. Хотя это может быть эффективным с какой-то предыдущей даты.

например Сотрудник Анна, как показано на изображении, присоединилась с 31 марта, и до сегодняшнего дня для этого сотрудника не было обновлений.

Следовательно, BI_EMPLOYEE имеет дату вступления в силу = 31 марта 2012 г.

Но позже могут быть перемещения к сотруднику на будущие даты, которые известны сейчас.

например Анна переезжает из Азии к нам 10 мая и так далее. Итак, с 10 мая ее регион будет у нас.

Итак, мне нужен отчет, который даст мне список всех сотрудников с указанием их статуса на каждую дату вступления в силу. В основном, все сотрудники, все действия и обновления в разные даты.

Мы также каждый день запускаем расписание, которое запускает все обновления и действия, т. е. вносит изменения в ТАБЛИЦУ BI_EMPLOYEE из таблицы обновлений и действий.

Итак, с 10 мая в расписании изменится регион с Азии на нас.

Я также приложил изображение с ожидаемым результатом.

Мы используем базу данных Oracle.

Пожалуйста помоги.

введите здесь описание изображениявведите здесь описание изображения

введите здесь описание изображениявведите здесь описание изображения

введите здесь описание изображения

В основном мне нужно создать представление, которое даст желаемый результат, который затем можно будет запросить в любое время для любого отчета.

ИЗМЕНИТЬ 1:

Ниже приведены DDL и DML.

        --------------------------------------------------------
    --  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);

Хорошо


sql oracle
person ashishjmeshram    schedule 25.05.2012    source источник
comment
Почему близко? Я пытался это в течение долгого времени без везения :(   -  person ashishjmeshram    schedule 25.05.2012
comment
Не согласен с закрытым голосованием - я сам сталкивался с таким требованием один или два раза.   -  person Jeffrey Kemp    schedule 25.05.2012
comment
@Джеффри Кемп. Привет. Удалось ли вам решить ее тогда? Любое предложение по этому поводу?   -  person ashishjmeshram    schedule 25.05.2012
comment
Извините, я сейчас в середине развертывания на работе. Может быть, позже у меня будет немного времени, чтобы посвятить вашей проблеме.   -  person Jeffrey Kemp    schedule 25.05.2012
comment
Неужели нет комментариев и ответов? :( :( :(   -  person ashishjmeshram    schedule 25.05.2012
comment
Вы поставили сложную задачу, это нормально. К сожалению, вы решили предоставить целую кучу изображений вместо примеров структуры таблиц и примеров данных. Таким образом, вы усложнили людям задачу помочь вам, потому что требуете больших усилий с нашей стороны. Также в SO работают волонтеры: у вас с нами нет SLA и вы не имеете права на ответ. Если вы недовольны ответом, вы должны отредактировать свой вопрос, чтобы предоставить DDL и DML, которые мы можем использовать для создания тестового примера.   -  person APC    schedule 25.05.2012
comment
@APC. Извините, я думал, что изображения помогут визуализировать структуру. Я отредактирую вопрос, чтобы предоставить DDL и DML.   -  person ashishjmeshram    schedule 25.05.2012
comment
Большинство из нас мыслит кодом. Схематическая модель данных была бы полезна, но все же не так полезна, когда дело доходит до написания запроса.   -  person APC    schedule 25.05.2012
comment
Скрипт имеет ошибки. В «UK» отсутствует апостроф, а вставка в BI_EMPLOYEE_ACTIVITY упоминает столбцы, которых нет в самой таблице.   -  person Nikola Markovinović    schedule 25.05.2012
comment
@Никола Марковинович. Изменил скрипт сейчас.   -  person ashishjmeshram    schedule 26.05.2012
comment
@Джеффри Кемп. У вас была возможность взглянуть на проблему?   -  person ashishjmeshram    schedule 26.05.2012
comment
Схема еще не очень. В Insert into BI_EMPLOYEE_ACTIVITY есть лишняя запятая; после этого он указывает invalid number. Пожалуйста, внесите свой вклад.   -  person Nikola Markovinović    schedule 26.05.2012

Ответы (1)


arrow_upward
1
arrow_downward

Во-первых, лучшей структурой данных является наличие даты вступления в силу и даты окончания в данных. Таким образом, вы можете увидеть, какая запись активна в любую дату.

К счастью, вы можете получить дату окончания в Oracle с помощью следующего запроса:

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

Этот запрос просматривает следующую дату вступления в силу, используя аналитическую функцию «лид». Затем он вычитает один день, чтобы получить дату окончания текущей записи со значением по умолчанию 01 января 9999 года для текущей записи.

Используя этот запрос, вы можете получить информацию о сотруднике на любую дату, используя предложение between:

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

Можете ли вы взять запрос отсюда?

person Gordon Linoff    schedule 25.05.2012
comment
Спасибо за ответ. Извините, но я не уверен, что понимаю это полностью. :( - person ashishjmeshram; 25.05.2012
comment
Я перефразировал некоторые вещи. . . это помогает? - person Gordon Linoff; 25.05.2012
comment
Извините, пока не повезло :(. Также я бы не хотел передавать какую-либо дату для запроса. Просто получите все данные для всех сотрудников в разные даты. - person ashishjmeshram; 25.05.2012