I am trying to use a pivot to display 2 specific tasks and the date on which the tasks were either Completed or are scheduled to Complete.
Here is the same code:
DROP TABLE TASKS;
CREATE TABLE TASKS
(
DOCUMENT_NUMBER NUMBER(9),
TASK_TYPE VARCHAR2(8),
TASK_STATUS VARCHAR2(8),
ACTUAL_COMPLETION_DATE DATE,
SCHEDULED_COMPLETION_DATE DATE);
INSERT INTO TASKS VALUES (2269734, 'DD', 'Complete',TO_DATE('6/1/2015 6:21:06','MM/DD/YYYY HH:MI:SS') ,TO_DATE('6/18/2015 12:45:00','MM/DD/YYYY HH:MI:SS'));
INSERT INTO TASKS VALUES (2269734, 'CAD', 'Pending', NULL, TO_DATE('6/25/2015 12:45:00','MM/DD/YYYY HH:MI:SS'));
INSERT INTO TASKS VALUES (2269734, 'RAD','Complete',TO_DATE('6/1/2015 6:24:10','MM/DD/YYYY HH:MI:SS'),TO_DATE('6/18/2015 12:30:00 ','MM/DD/YYYY HH:MI:SS'));
Here is the query that I am using thus far:
SELECT DISTINCT MSTAT.*
FROM
(SELECT TASKS.DOCUMENT_NUMBER,
-- TASKS.TASKS_TYPE,
-- TASKS.TASKS_STATUS,
TASKS.ACTUAL_COMPLETION_DATE DD_COMP_DT,
TASKS_1.TASK_TYPE PTYP,
TASKS_1.TASK_STATUS PSTAT
,
CASE WHEN TASKS_1.TASK_TYPE = 'CAD'
AND TASKS_1.TASK_STATUS IN ('Pending','Ready')
THEN TASKS_1.SCHEDULED_COMPLETION_DATE
WHEN TASKS_1.TASK_TYPE = 'CAD'
AND TASKS_1.TASK_STATUS = 'Complete'
THEN TASKS_1.ACTUAL_COMPLETION_DATE
END as CAD_COMP_DT
-- TASKS_1.SCHEDULED_COMPLETION_DATE
FROM TASKS
LEFT OUTER JOIN TASKS TASKS_1
ON (TASKS.DOCUMENT_NUMBER = TASKS_1.DOCUMENT_NUMBER)
WHERE (TASKS.DOCUMENT_NUMBER = 2269734) AND (TASKS.TASK_TYPE = 'DD'))
PIVOT(max(PSTAT), max(DD_COMP_DT) as DD_COMP_DT, max(CAD_COMP_DT) as CAD_COMP_DT for PTYP in ('DD' DD,'CAD' CAD)) MSTAT
My results are as shown below:
| DOCUMENT_NUMBER |
DD |
DD_DD_COMP_DT |
DD_CAD_COMP_DT |
CAD |
CAD_DD_COMP_DT |
CAD_CAD_COMP_DT |
| 2269734 |
Complete |
6/1/2015 |
|
Pending |
6/1/2015 |
6/25/2015 |
My desired result is as follows:
| DOCUMENT_NUMBER |
DD |
DD_DD_COMP_DT |
CAD |
CAD_CAD_COMP_DT |
| 2269734 |
Complete |
6/1/2015 |
Pending |
6/25/2015 |
I appreciate anyone taking the time to look and suggest the correct way to implement.
Thanks
G