CODEWITHSUNDEEP
sqloracle-database
nsc909
nsc909

Reputation: 25

Oracle SQL - how to select and combine data from multiple rows

The scenario is that for a particular date range, I need to return all rows where a field value has changed (FROM and TO date). The changed field values will then be shown on screen in a different colour.

I can return all employee records where a change has occurred for the specific time period. However, if an employee's record has changed multiple times within the selected time period, I need to return a single employee record with a combined value for each of the different X_FLAG columns. 1 indicates that a change has occurred, 0 indicates no change.

Table DDL is:

CREATE TABLE "EMPLOYEE_DATA" 
   (    "EMPLOYEE_ID" NUMBER(20,0), 
    "EMPLOYEE_NAME" VARCHAR2(100 BYTE), 
    "EMPLOYEE_NAME_FLAG" NUMBER(1,0), 
    "EMPLOYEE_ROLE" VARCHAR2(100 BYTE), 
    "EMPLOYEE_ROLE_FLAG" NUMBER(1,0), 
    "EMPLOYEE_SALARY" VARCHAR2(100 BYTE), 
    "EMPLOYEE_SALARY_FLAG" NUMBER(1,0),  
    "DATE_VALID_FROM" DATE, 
    "DATE_VALID_TO" DATE, 
    "HAS_RECORD_CHANGED" NUMBER(1,0), 
    "CURRENT_ROW_IND" NUMBER(1,0)
   );

Mock data is:

Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (1,'John Smith',0,'Associate',0,'1',0,to_date('01-FEB-17','DD-MON-RR'),to_date('28-FEB-17','DD-MON-RR'),0,0);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (2,'Katy Brown',0,'Team Leader',0,'7',0, to_date('01-FEB-17','DD-MON-RR'),to_date('28-FEB-17','DD-MON-RR'),0,0);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (2,'Katy Brown',0,'Team Leader',0,'7',0, to_date('01-APR-17','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),1,1);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (3,'Ian Jones',1,'Delivery Manager',1,'3',1, to_date('01-MAR-17','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),1,1);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (1,'John Smith',0,'Analyst',1,'1',0, to_date('01-MAR-17','DD-MON-RR'),to_date('31-MAR-17','DD-MON-RR'),1,0);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (1,'John Smith',0,'Analyst',0,'2',1, to_date('01-APR-17','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),1,1);

My query is:

SELECT *
FROM EMPLOYEE_DATA
WHERE DATE_VALID_FROM <= TO_DATE('01/04/2017', 'dd/mm/yyyy') 
  AND DATE_VALID_TO >= TO_DATE('01/01/2017', 'dd/mm/yyyy')
  AND HAS_RECORD_CHANGED = '1'
ORDER BY EMPLOYEE_ID ASC, DATE_VALID_FROM ASC;

In my final query, I will add following line to bring back the current record AND CURRENT_ROW_IND = '1'. I left this out to show how I need to combine data for "John Smith" record to merge EMPLOYEE_ROLE_FLAG and EMPLOYEE_SALARY_FLAG for the previous and current record (for John Smith)

EDIT - Added original and target results. If possible, I would need to aggregate to get the max of the X_FLAG columns for each unique employee.

Original 

EMPLOYEE_ID    EMPLOYEE_NAME    EMPLOYEE_NAME_FLAG  EMPLOYEE_ROLE   EMPLOYEE_ROLE_FLAG  EMPLOYEE_SALARY EMPLOYEE_SALARY_FLAG    DATE_VALID_FROM DATE_VALID_TO   HAS_RECORD_CHANGED  CURRENT_ROW_IND
1              John Smith       0                   Associate               0                   1               0                   01-Feb-17         28-Feb-17     0                   0
2              Katy Brown       0                   Team Leader             0                   7               0                   01-Feb-17         28-Feb-17     0                   0
2              Katy Brown       0                   Team Leader             0                   7               0                   01-Apr-17         31-Dec-99     1                   1
3              Ian Jones        1                   Delivery Manager        1                   3               1                   01-Mar-17         31-Dec-99     1                   1
1              John Smith       0                   Analyst                 1                   0               0                   01-Mar-17         31-Mar-17     1                   0
1              John Smith       0                   Analyst                 0                   1               1                   01-Apr-17         31-Dec-99     1                   1

Target

EMPLOYEE_ID EMPLOYEE_NAME   EMPLOYEE_NAME_FLAG  EMPLOYEE_ROLE   EMPLOYEE_ROLE_FLAG  EMPLOYEE_SALARY EMPLOYEE_SALARY_FLAG    DATE_VALID_FROM DATE_VALID_TO   HAS_RECORD_CHANGED  CURRENT_ROW_IND
1           John Smith      0                   Analyst                     1                   1               1               01-Apr-17    31-Dec-99      1                   1
2           Katy Brown      0                   Team Leader                 0                   7               0               01-Apr-17    31-Dec-99      1                   1
3           Ian Jones       1                   Delivery Manager            1                   3               1               01-Mar-17    31-Dec-99      1                   1

Upvotes: 2

Views: 123

Answers (2)

Pons
Pons

Reputation: 1111

If possible, I would need to aggregate to get the max of the X_FLAG columns for each unique employee.

For the above requirement GROUP BY will work, right?

Please find below query to achieve you Target,

SELECT EMPLOYEE_ID
  , EMPLOYEE_NAME
  , MIN(EMPLOYEE_NAME_FLAG) EMPLOYEE_NAME_FLAG
  , EMPLOYEE_ROLE
  , MAX(EMPLOYEE_ROLE_FLAG) EMPLOYEE_ROLE_FLAG
  , MIN(EMPLOYEE_SALARY) EMPLOYEE_SALARY
  , MAX(EMPLOYEE_SALARY_FLAG) EMPLOYEE_SALARY_FLAG
  , MAX(DATE_VALID_FROM) DATE_VALID_FROM
  , MAX(DATE_VALID_TO) DATE_VALID_TO
  , HAS_RECORD_CHANGED
  , MAX(CURRENT_ROW_IND) CURRENT_ROW_IND
FROM EMPLOYEE_DATA
WHERE HAS_RECORD_CHANGED = 1
   AND DATE_VALID_FROM BETWEEN TO_DATE('01/01/2017', 'dd/mm/yyyy')
                             AND TO_DATE('01/04/2017', 'dd/mm/yyyy')
GROUP BY EMPLOYEE_ID
  , EMPLOYEE_NAME
  , EMPLOYEE_ROLE
  , HAS_RECORD_CHANGED
ORDER BY EMPLOYEE_ID ASC
  , DATE_VALID_FROM ASC;

Upvotes: 1

Parfait
Parfait

Reputation: 107567

Consider derived tables where unit level query joins with aggregate query that calculates max flags:

SELECT emp.*    
FROM
  (SELECT *
   FROM EMPLOYEE_DATA
   WHERE DATE_VALID_FROM BETWEEN TO_DATE('01/01/2017', 'dd/mm/yyyy')
                             AND TO_DATE('01/04/2017', 'dd/mm/yyyy')
     AND HAS_RECORD_CHANGED = 1
  ) AS emp

INNER JOIN
  (SELECT EMPLOYEE_ID, MAX(EMPLOYEE_NAME_FLAG) AS MAX_NAME_FLAG,
          MAX(EMPLOYEE_ROLE_FLAG) AS MAX_ROLE_FLAG, 
          MAX(EMPLOYEE_SALARY_FLAG) AS MAX_SALARY_FLAG
   FROM EMPLOYEE_DATA
   WHERE DATE_VALID_FROM BETWEEN TO_DATE('01/01/2017', 'dd/mm/yyyy')
                             AND TO_DATE('01/04/2017', 'dd/mm/yyyy')
     AND HAS_RECORD_CHANGED = 1
   GROUP BY EMPLOYEE_ID
  ) AS agg

ON emp.EMPLOYEE_ID = agg.EMPLOYEE_ID 
AND emp.EMPLOYEE_NAME_FLAG = agg.MAX_NAME_FLAG 
AND emp.EMPLOYEE_ROLE_FLAG = agg.MAX_ROLE_FLAG 
AND emp.EMPLOYEE_SALARY_FLAG = agg.MAX_SALARY_FLAG

ORDER BY emp.EMPLOYEE_ID ASC, emp.DATE_VALID_FROM ASC

Upvotes: 1

Related Questions