Как да получите данните за служителите към различни дати на влизане в сила?

Моля, вижте следните изображения. По същество това е как изглежда моята база данни.

Таблицата BI_EMPLOYEE винаги има текущия запис към днешна дата. Въпреки че може да е в сила от някаква предишна дата.

напр. Служителят Анна, както е показано на изображението, се присъедини от 31 март и до днес няма направени актуализации за този служител.

Следователно BI_EMPLOYEE има ефективна дата = 31 март 2012 г.

Но по-късно може да има премествания на служител на бъдещи дати, което се знае сега.

напр. anna се мести от азия у нас на 10 май и т.н. И така, в сила от 10 май нейният регион ще бъдем ние.

И така, имам нужда от отчет, който ще ми даде списък на всички служители с техния статус към всяка ефективна дата. По принцип всички служители извършват всички дейности и актуализации на различни дати.

Ние също изпълняваме график всеки ден, който изпълнява всички актуализации и дейности, т.е. прави промени в BI_EMPLOYEE TABLE от актуализацията и таблицата с дейности.

И така, тъй като на 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

Тази заявка разглежда следващата ефективна дата, като използва аналитичната функция "lead". След това изважда един ден, за да получи крайната дата за текущия запис със стойност по подразбиране 01 януари 9999 за текущия запис.

Използвайки тази заявка, можете да получите информация за служител на всяка дата, като използвате клауза между:

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