nsc909 nsc909 - 9 months ago 26
SQL Question

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

Answer Source

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