6 Replies Latest reply: Sep 7, 2013 11:08 AM by Ora-aff RSS

    unable to retrive the data using self join

    Ora-aff

      I am trying to find the time taken to close a ticket. The ticket goes through various stages: NEW, INPROCESS, CLOSED, REOPENED. If the ticket is reopened then the next stage would be INPROCESS and then CLOSED.

      The CC_TICKET_INFO table contains information about the last stage of the ticket. CC_TICKET_HISTORY table contains information about all the stages of the ticket.

      The challenge here is if the ticket is reopened then it should be counted as 2 instances instead of 1.

      First instance from stages: NEW --> INPROCESS --> CLOSE

      Second instance from stages: REOPEND --> INPROCESS --> CLOSE.

       

      Follwoing SQL is to generate the data:

       

      CREATE TABLE CC_TICKET_INFO

      (

        TICKET_ID VARCHAR2(20 BYTE) NOT NULL

      , TICKET_STATUS VARCHAR2(60 BYTE)

      , created_date timestamp(6)

      , LAST_CHANGED timestamp(6)

      , ASSIGNED_TO VARCHAR2(20)

      , CONSTRAINT PK_CC_TICKET_INFO PRIMARY KEY

        (

          ticket_id

        )

      )

       

       

      CREATE TABLE CC_TICKET_HISTORY

      (

        TICKET_ID VARCHAR2(20 BYTE) NOT NULL

      , TICKET_STATUS VARCHAR2(60 BYTE)

      , CREATED_DATE TIMESTAMP(6) NOT NULL

      , ASSIGNED_TO VARCHAR2(255 BYTE)

      , CREATED_BY VARCHAR2(60 BYTE)

      , CONSTRAINT PK_CC_TICKET_HISTORY PRIMARY KEY

        (

          TICKET_ID

        , CREATED_DATE

        )

      )

       

       

      insert into cc_ticket_history values ('D21207155', 'NEW', '6/28/2013 17:28:59', null, 'jsg-st');

      insert into cc_ticket_history values ('D21207155', 'INPROCESS', '6/28/2013 17:48:19', 'ah-eg', 'ah-eg');

      insert into cc_ticket_history values ('D21207155', 'CLOSED', '6/28/2013 18:54:23', 'ah-eg', 'ah-eg');

      insert into cc_ticket_history values ('D21207155', 'REOPENED', '7/2/2013 19:55:04', 'ah-eg', 'jsg-st');

      insert into cc_ticket_history values ('D21207155', 'INPROCESS', '7/2/2013 20:11:17', 'sr-eg', 'sr-eg');

      insert into cc_ticket_history values ('D21207155', 'CLOSED', '7/2/2013 23:06:16', 'sr-eg', 'sr-eg');

       

       

      insert into CC_TICKET_INFO values ('D21207155', 'CLOSED', '6/28/2013 17:28:59', '7/2/2013 23:06:16', 'sr-eg');

       

      ______________________________________________________________________

       

       

      I want to find the time difference betwen '6/28/2013 18:54:23' and '6/28/2013 17:28:59' (First instance)

      and between '7/2/2013 23:06:16' and '7/2/2013 19:55:04' (Second instance)

       

      The closest I am getting is the through this query:

       

      select L.ticket_id, L.CREATED_DATE, R.CREATED_DATE as close_date, L.TICKET_STATUS, R.TICKET_STATUS from cc_ticket_history L RIGHT join cc_ticket_history R on (L.rowid < R.rowid) where (L.TICKET_STATUS = 'NEW' AND R.TICKET_STATUS ='CLOSED') OR (L.TICKET_STATUS = 'REOPENED' AND R.TICKET_STATUS ='CLOSED');

       

       

       

      Can it be done through SQL?

        • 1. Re: unable to retrive the data using self join
          Etbin

          select ticket_id,ticket_status,created_date,

                 case when ticket_status = 'CLOSED'

                       and lead(ticket_status) over (partition by ticket_id order by created_date) = 'REOPENED'

                      then lead(created_date) over (partition by ticket_id order by created_date) - created_date

                      when ticket_status = 'REOPENED'

                       and lag(ticket_status) over (partition by ticket_id order by created_date) = 'CLOSED'

                      then created_date - lag(created_date) over (partition by ticket_id order by created_date)

                 end time_difference

            from cc_ticket_history

          where ticket_status in ('CLOSED','REOPENED')

           

          TICKET_IDTICKET_STATUSCREATED_DATETIME_DIFFERENCE
          D21207155CLOSED28-JUN-13 06.54.23.000000 PM+000000004 01:00:41.000000
          D21207155REOPENED02-JUL-13 07.55.04.000000 PM+000000004 01:00:41.000000
          D21207155CLOSED02-JUL-13 11.06.16.000000 PM-

           

          Regards

           

          Etbin

          • 2. Re: unable to retrive the data using self join
            Ora-aff

            Etbin Thanks for the answer. I think you are closer!!

            The first 3 insert statements above form the first instance. The second 3 inserts statements form the second instance.

            Ideally, I would like the output to be something like this (values taken from above insert statements):

             

            Ticket_ID           STATUS                  CREATED_DATE                                 CLOSED_DATE                          TIME_DIFFERENCE

            D21207155        CLOSED                 6/28/2013 17:28:59                               6/28/2013 18:54:23                      

            D21207155        CLOSED                 7/2/2013 19:55:04                                 7/2/2013 23:06:16

            • 3. Re: unable to retrive the data using self join
              Frank Kulash

              Hi,

               

              Ora-aff wrote:

               

              Etbin Thanks for the answer. I think you are closer!!

              The first 3 insert statements above form the first instance. The second 3 inserts statements form the second instance.

              Ideally, I would like the output to be something like this (values taken from above insert statements):

               

              Ticket_ID           STATUS                  CREATED_DATE                                 CLOSED_DATE                          TIME_DIFFERENCE

              D21207155        CLOSED                 6/28/2013 17:28:59                               6/28/2013 18:54:23                      

              D21207155        CLOSED                 7/2/2013 19:55:04                                 7/2/2013 23:06:16

               

              You can do that using the analytic LAST_VALUE function:

               

              WITH    got_opened_date   AS

              (

                  SELECT  ticket_id, ticket_status, created_date

                  ,       LAST_VALUE ( CASE

                                           WHEN  ticket_status  IN ('NEW', 'REOPENED')

                                           THEN  created_date

                                       END

                                       IGNORE NULLS

                                     ) OVER ( PARTITION BY  ticket_id

                                              ORDER BY      created_date

                                            )     AS opened_date

                  FROM    cc_ticket_history

              )

              SELECT    ticket_id

              ,         ticket_status   AS status

              ,         opened_date     AS created_date

              ,         created_date    AS closed_date

              ,         NULL            AS time_difference

              FROM      got_opened_date

              WHERE     ticket_status    = 'CLOSED'

              ORDER BY  ticket_id

              ,         closed_date

              ;

              • 4. Re: unable to retrive the data using self join
                Ora-aff

                Thanks FrankKulash. That works.

                 

                However, I am going with the following query that I managed to write after reading about LEAD and LAG:

                 

                 

                SELECT  b.ticket_id, b.ticket_status, b.created_date, b.closed_status, b.closed_date
                  FROM
                  (SELECT  ticket_id, ticket_status, created_date, lead(ticket_status) over (partition BY ticket_id order by created_date) AS closed_status, lead(created_date) over (partition BY ticket_id order by created_date) AS closed_date
                  FROM cc_ticket_history
                  WHERE ticket_status IN ('NEW', 'REOPENED', 'CLOSED')
                  ) b
                  WHERE ticket_status IN ('NEW', 'REOPENED');
                
                Ticket_IDTICKET_STATUSCREATED_DATECLOSED_STATUSCLOSED_DATE
                D21207155NEW06/28/2013 17.28.59.000000000CLOSED06/28/2013 18.54.23.000000000
                D21207155REOPENED07/02/2013 19.55.04.000000000CLOSED07/02/2013 23.06.16.000000000

                 

                 

                Just one question though: What is the significance of partition BY clause. Beacsue the same query works if I don't include the partition by clasue. My understanding is that it becomes significant when there will be more that one tickets. It sorts of groups by the rows based on ticket_id before applying the order by on that group???? Am I right?

                • 5. Re: unable to retrive the data using self join
                  Etbin

                  Sorry, I got it wrong: your OP indicated

                  I want to find the time difference betwen '6/28/2013 18:54:23' and '6/28/2013 17:28:59'

                  taking into account your best attempt too

                   

                  Try to add some different ticket_id data  to find out the sighificance of patrition by:

                  When partition by is present the analytic function operates within each ticket_id group, without partition by the analytic function operates on the table as a whole.

                  In your case (having just one ticket_id) it makes no difference whether partition by clause is present or not.

                  Frank would have explained it better for sure.

                   

                  Regards

                   

                  Etbin

                  • 6. Re: unable to retrive the data using self join
                    Ora-aff

                    Not a problem Etbin.

                    Actually your query got me going. It was very helpful. I will add more data and test it.