Forum Stats

  • 3,853,644 Users
  • 2,264,249 Discussions
  • 7,905,425 Comments

Discussions

PIVOT

Gmoney
Gmoney Member Posts: 148
edited Jun 20, 2015 5:34AM in SQL & PL/SQL

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

Tagged:
myCloud

Best Answer

  • James Su
    James Su Member Posts: 1,165 Gold Trophy
    edited Jun 19, 2015 3:52PM Answer ✓

    SELECT TASKS.DOCUMENT_NUMBER, 

           MAX(CASE WHEN TASK_TYPE='DD' THEN ACTUAL_COMPLETION_DATE END) DD_COMP_DT, 

           MAX(CASE WHEN TASK_TYPE='DD' THEN TASK_STATUS END) DD_STAT,

      MAX(CASE WHEN TASK_TYPE = 'CAD' 

           AND TASK_STATUS IN ('Pending','Ready') 

        THEN SCHEDULED_COMPLETION_DATE 

        WHEN TASK_TYPE = 'CAD' 

           AND TASK_STATUS = 'Complete' 

        THEN ACTUAL_COMPLETION_DATE 

      END) as CAD_COMP_DT,

           MAX(CASE WHEN TASK_TYPE='CAD' THEN TASK_STATUS END) DD_STAT

      FROM TASKS 

    WHERE  TASKS.DOCUMENT_NUMBER = 2269734

    GROUP BY DOCUMENT_NUMBER

    Gmoney

Answers

  • James Su
    James Su Member Posts: 1,165 Gold Trophy
    edited Jun 19, 2015 3:52PM Answer ✓

    SELECT TASKS.DOCUMENT_NUMBER, 

           MAX(CASE WHEN TASK_TYPE='DD' THEN ACTUAL_COMPLETION_DATE END) DD_COMP_DT, 

           MAX(CASE WHEN TASK_TYPE='DD' THEN TASK_STATUS END) DD_STAT,

      MAX(CASE WHEN TASK_TYPE = 'CAD' 

           AND TASK_STATUS IN ('Pending','Ready') 

        THEN SCHEDULED_COMPLETION_DATE 

        WHEN TASK_TYPE = 'CAD' 

           AND TASK_STATUS = 'Complete' 

        THEN ACTUAL_COMPLETION_DATE 

      END) as CAD_COMP_DT,

           MAX(CASE WHEN TASK_TYPE='CAD' THEN TASK_STATUS END) DD_STAT

      FROM TASKS 

    WHERE  TASKS.DOCUMENT_NUMBER = 2269734

    GROUP BY DOCUMENT_NUMBER

    Gmoney
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,496 Red Diamond
    edited Jun 19, 2015 4:34PM

    Hi,

    How about:

    SELECT  d.document_number
    ,       d.task_status              AS dd_task_status
    ,       d.actual_completion_date   AS dd_completion_date
    ,       c.task_status              AS cad_task_status
    ,       CASE  c.task_status
                WHEN  'Pending'  THEN  c.scheduled_completion_date
                WHEN  'Ready'    THEN  c.scheduled_completion_date
                WHEN  'Complete' THEN  c.actual_completion_date
            END                        AS cad_completion_date
    FROM             tasks  d
    LEFT OUTER JOIN  tasks  c  ON   c.document_number  = d.document_number
                               AND  c.task_type        = 'CAD'
    WHERE   d.task_type  = 'DD'
    ;
    
    
    myCloudGmoney
  • Gmoney
    Gmoney Member Posts: 148
    edited Jun 19, 2015 7:40PM

    James - thanks for the quick reply. I am really wanting to use the PIVOT function if at all possible. this is for an issue at work, but I am also looking top expand my own knowledge on on how to apply the PIVOT in different scenarios. Do you have any suggestions on modifying what I currently have and maintaining the use of PIVOT?

    Thank you

    Grerg

  • Gmoney
    Gmoney Member Posts: 148
    edited Jun 19, 2015 7:42PM

    Frank,

    Your method is very clean and concise. Thank you for the response. Can you tell me if there is a way to utilize the PIVOT as in my original post?

  • Gmoney
    Gmoney Member Posts: 148
    edited Jun 19, 2015 7:47PM

    One thing changed from my original post in your response was that you treated it as 1 table that I was sourcing , when in fact there are 2 task tables. that is if I am reading your solution correctly.

  • James Su
    James Su Member Posts: 1,165 Gold Trophy
    edited Jun 19, 2015 8:50PM

    It's actually one table, why bother to do a self join? If the table is small it doesn't really matter, otherwise you may want to compare the performance of different approaches.

    SELECT *

      FROM (select DOCUMENT_NUMBER, TASK_TYPE, ACTUAL_COMPLETION_DATE,TASK_STATUS, SCHEDULED_COMPLETION_DATE from tasks 

             WHERE  TASKS.DOCUMENT_NUMBER = 2269734

           )

           PIVOT(MAX(CASE WHEN TASK_TYPE='DD' THEN ACTUAL_COMPLETION_DATE

                      WHEN TASK_TYPE = 'CAD'

                           AND TASK_STATUS IN ('Pending','Ready')

                      THEN SCHEDULED_COMPLETION_DATE

                      WHEN TASK_TYPE = 'CAD'

                         AND TASK_STATUS = 'Complete'

                      THEN ACTUAL_COMPLETION_DATE

                    END) AS COMP_DT,MAX(TASK_STATUS) AS STAT  for TASK_TYPE in ('DD' DD,'CAD' CAD));

    Gmoney
  • Stew Ashton
    Stew Ashton Member Posts: 2,911 Bronze Crown
    edited Jun 20, 2015 5:34AM

    This is not a new solution: it is a slight rewrite of the solution provided by James. The only differences are a slight simplification of the OP's logic and more separation of that logic from the PIVOT functionality.

    select * from (
      select document_number, task_type, task_status,
      case task_status when 'Complete' then actual_completion_date
        else scheduled_completion_date
      end comp_dt
      from tasks
      where task_type in ('DD', 'CAD')
    )
    pivot(
      max(task_status) status, max(comp_dt) comp_dt
      for task_type in ('DD' DD, 'CAD' CAD)
    );
    

    DOCUMENT_NUMBERDD_STATUSDD_COMP_DTCAD_STATUSCAD_COMP_DT
    2269734Complete2015-06-01Pending2015-06-25
    Gmoney
This discussion has been closed.