7 Replies Latest reply: May 7, 2013 3:46 PM by madmac RSS

    Get the MAX(timestamp) and use it on a LEFT OUTER JOIN

    madmac
      Hello All,
      Help! I can't figure this one out. This query works but data results are not correct. Right now I am using LEFT OUTER JOIN several times. The reason the output is incorrect is that there may be several records for a custom field with a different time stamp (created_ts). Created_ts is in the TKT_history table. I have to get the most recent entry for each custom field. I have tried to to use mac(created_ts) but can't get it to work.

      So for 1 ticket_id there may be one entry for custom_76, no entry for custom_89, and 2 enteries per remaining custom field. Or any other possible combination.

      How can I get the most current record for each custom data field?

      SELECT
      creator,
      email,
      DECODE(channel,179, 'NY',178, NJ',180, 'MA',182, 'NE') channel,
      nchannel,
      mth,
      cmth,
      DECODE(region, 376, 'MA',379, 'RI',561, 'NYSE',562, 'NYNW') region,
      nregion,
      type_event,
      lead_status,
      count(leadnumber) cleadnumber,
      SUM(data) data,
      SUM(tv) tv,
      SUM(hsi) shsi,
      SUM(new_vln) snew_vln,
      SUM(winback_ln) swinback_ln
      FROM
      (SELECT
      a.creator creator,
      a.ticket_id leadnumber,
      TO_CHAR(a.entrydate, 'MM') mth,
      TO_CHAR(a.entrydate, 'Month') cmth,
      a.grp channel,
      a.grp nchannel,
      a.region region,
      a.region nregion,
      b.new_value type_event,
      c.new_value lead_status,
      d.new_value data,
      e.new_value tv,
      f.new_value hsi,
      g.new_value new_vln,
      h.new_value winback_ln,
      i.vexternalemail email
      FROM
      TKT_ticket a
      LEFT OUTER JOIN TKT_history b ON (a.ticket_id = b.ticket_id AND b.field_name = 'custom_76')
      LEFT OUTER JOIN TKT_history c ON (a.ticket_id = c.ticket_id AND c.field_name = 'custom_89')
      LEFT OUTER JOIN TKT_history d ON (a.ticket_id = d.ticket_id AND d.field_name = 'custom_91')
      LEFT OUTER JOIN TKT_history e ON (a.ticket_id = e.ticket_id AND e.field_name = 'custom_92')
      LEFT OUTER JOIN TKT_history f ON (a.ticket_id = f.ticket_id AND f.field_name = 'custom_93')
      LEFT OUTER JOIN TKT_history g ON (a.ticket_id = g.ticket_id AND g.field_name = 'custom_95')
      LEFT OUTER JOIN TKT_history h ON (a.ticket_id = h.ticket_id AND h.field_name = 'custom_96')
      LEFT OUTER JOIN LDAP_userinfo i ON (a.creator = i.emp_id AND i.isActive = 1)
      WHERE a.entrydate >= TO_DATE('2013-04-30','YYYY-MM-DD')
      AND a.entrydate < TO_DATE('2013-04-30','YYYY-MM-DD') + 1
      AND a.creator IN ('36316','36389','38129','36303','35344','34189','36323','32051','36399','36395','242756')
      AND a.grp IN ('1790','1787','1791','1782')
      AND TO_CHAR(a.entrydate,'MM') IN ('01','02','03','04','05','06','07','08','09','10','11','12')
      AND a.region IN ('376','379','561','562')
      AND NVL(c.new_value,'Blank') IN ('No Sale','Sold','Pending','Blank')
      AND b.new_value IN ('HERE,'THERE','EVERYWHERE')
      )
      GROUP BY
      creator,
      email,
      mth,
      cmth,
      channel,
      nchannel,
      region,
      nregion,
      type_event,
      lead_status
      ORDER BY
      email,
      channel,
      mth,
      cmth,
      region,
      type_event,
      lead_status

      Edited by: madmac on May 3, 2013 12:09 PM
        • 1. Re: Get the MAX(timestamp) and use it on a LEFT OUTER JOIN
          Etbin
          Maybe
          select creator,
                 email,
                 decode(channel,179, 'NY',178, NJ',180, 'MA',182, 'NE') channel,
                 nchannel,
                 mth,
                 cmth,
                 decode(region, 376, 'MA',379, 'RI',561, 'NYSE',562, 'NYNW') region,
                 nregion,
                 type_event,
                 lead_status,
                 count(leadnumber) cleadnumber,
                 sum(data) data,
                 sum(tv) tv,
                 sum(hsi) shsi,
                 sum(new_vln) snew_vln,
                 sum(winback_ln) swinback_ln
            from (select a.creator creator,
                         a.ticket_id leadnumber,
                         to_char(a.entrydate, 'MM') mth,
                         to_char(a.entrydate, 'Month') cmth,
                         a.grp channel, 
                         a.grp nchannel,
                         a.region region,
                         a.region nregion,
                         case when h.field_name = 'custom_76' then h.new_value end type_event,
                         case when h.field_name = 'custom_89' then h.new_value end lead_status,
                         case when h.field_name = 'custom_91' then h.new_value end data,
                         case when h.field_name = 'custom_92' then h.new_value end tv,
                         case when h.field_name = 'custom_93' then h.new_value end hsi,
                         case when h.field_name = 'custom_95' then h.new_value end new_vln,
                         case when h.field_name = 'custom_96' then h.new_value end winback_ln,
                         i.vexternalemail email
                    from tkt_ticket a
                         left outer join
                         (select ticket_id,field_name,new_value
                            from (select ticket_id,field_name,new_value,
                                         row_number() over (partition by ticket_id,field_name
                                                                order by created_ts desc
                                                           ) rn
                                    from tkt_history
                                   where substr(field_name,8) in ('76','89','91','92','93','94','95','96')
                                 )
                           where rn = 1
                         ) h
                      on (a.ticket_id = h.ticket_id)
                         left outer join ldap_userinfo i 
                     on (a.creator = i.emp_id and i.isactive = 1) 
                   where a.entrydate >= to_date('2013-04-30','yyyy-mm-dd')
                     and a.entrydate < to_date('2013-04-30','yyyy-mm-dd') + 1
                     and a.creator in ('36316','36389','38129','36303','35344','34189','36323','32051','36399','36395','242756')
                     and a.grp in ('1790','1787','1791','1782')
                     and to_char(a.entrydate,'mm') in ('01','02','03','04','05','06','07','08','09','10','11','12') /* ? */
                     and a.region in ('376','379','561','562')
                     and case when h.field_name = 'custom_89' 
                              then nvl(h.new_value,'Blank') 
                              else '?' 
                         end in ('No Sale','Sold','Pending','Blank')
                     and case when h.field_name = 'custom_76' 
                              then h.new_value 
                              else '?' 
                         end in ('HERE,'THERE','EVERYWHERE')
                 ) 
           group by creator,
                    email,
                    mth,
                    cmth,
                    channel,
                    nchannel,
                    region,
                    nregion,
                    type_event,
                    lead_status
           order by email,
                    channel,
                    mth,
                    cmth,
                    region,
                    type_event,
                    lead_status
          Regards

          Etbin

          Edited by: Etbin on 4.5.2013 6:57
          added <tt>where substr(field_name,8) in ('76','89','91','92','93','94','95','96')</tt> to exclude other columns
          <tt>substr</tt> used just to keep the line short
          • 2. Re: Get the MAX(timestamp) and use it on a LEFT OUTER JOIN
            Tubby
            Try incorporating something like
            select
               field_name, ticket_id
            from
            (
               select field_name, ticket_id, created_ts, max(Created_ts) over (partition by field_name) as max_created_ts
               from TKT_history
               where field_name in ('custom_76', .... <the rest of your literals>)
            )
            where created_ts = max_created_ts
            And then join that to your other data.

            Cheers,
            • 3. Re: Get the MAX(timestamp) and use it on a LEFT OUTER JOIN
              madmac
              This kinda works but I am not getting back the results I want. If I only have one of the custom fields for a ticket I get one record back with all the values null for the custom fields except for the custom field that the case statement ansswered. If I have more than 1 custom field per ticket I get back one record for each case that was answered. All the other custom fields are null. I need to get back one record for one ticket reguardless of how many custom fields it has. Any ideas?

              Edited by: madmac on May 6, 2013 12:04 PM
              • 4. Re: Get the MAX(timestamp) and use it on a LEFT OUTER JOIN
                Etbin
                Maybe
                select creator,
                       email,
                       decode(channel,179, 'NY',178, NJ',180, 'MA',182, 'NE') channel,
                       nchannel,
                       mth,
                       cmth,
                       decode(region, 376, 'MA',379, 'RI',561, 'NYSE',562, 'NYNW') region,
                       nregion,
                       type_event,
                       lead_status,
                       count(leadnumber) cleadnumber,
                       sum(data) data,
                       sum(tv) tv,
                       sum(hsi) shsi,
                       sum(new_vln) snew_vln,
                       sum(winback_ln) swinback_ln
                  from (select a.creator creator,
                               a.ticket_id leadnumber,
                               to_char(a.entrydate, 'MM') mth,
                               to_char(a.entrydate, 'Month') cmth,
                               a.grp channel, 
                               a.grp nchannel,
                               a.region region,
                               a.region nregion,
                               h.type_event,
                               h.lead_status,
                               h.data,
                               h.tv,
                               h.hsi,
                               h.new_vln,
                               h.winback_ln,
                               i.vexternalemail email
                          from tkt_ticket a
                               left outer join
                               (select ticket_id,
                                       max(case when field_name = 'custom_76' then new_value end) type_event,
                                       max(case when field_name = 'custom_89' then new_value end) lead_status,
                                       sum(case when field_name = 'custom_91' then new_value end) data,
                                       sum(case when field_name = 'custom_92' then new_value end) tv,
                                       sum(case when field_name = 'custom_93' then new_value end) hsi,
                                       sum(case when field_name = 'custom_95' then new_value end) new_vln,
                                       sum(case when field_name = 'custom_96' then new_value end) winback_ln
                                  from (select ticket_id,field_name,new_value
                                          from (select ticket_id,field_name,new_value,
                                                       row_number() over (partition by ticket_id,field_name
                                                                              order by created_ts desc
                                                                         ) rn
                                                  from tkt_history
                                                 where substr(field_name,8) in ('76','89','91','92','93','94','95','96')
                                               )
                                         where rn = 1
                                       )
                                 group by ticket_id
                               ) h
                            on (a.ticket_id = h.ticket_id)
                               left outer join ldap_userinfo i 
                           on (a.creator = i.emp_id and i.isactive = 1) 
                         where a.entrydate >= to_date('2013-04-30','yyyy-mm-dd')
                           and a.entrydate < to_date('2013-04-30','yyyy-mm-dd') + 1
                           and a.creator in ('36316','36389','38129','36303','35344','34189','36323','32051','36399','36395','242756')
                           and a.grp in ('1790','1787','1791','1782')
                           and to_char(a.entrydate,'mm') in ('01','02','03','04','05','06','07','08','09','10','11','12') /* ? */
                           and a.region in ('376','379','561','562')
                           and case when h.field_name = 'custom_89' 
                                    then nvl(h.new_value,'Blank') 
                                    else '?' 
                               end in ('No Sale','Sold','Pending','Blank')
                           and case when h.field_name = 'custom_76' 
                                    then h.new_value 
                                    else '?' 
                               end in ('HERE,'THERE','EVERYWHERE')
                       ) 
                 group by creator,
                          email,
                          mth,
                          cmth,
                          channel,
                          nchannel,
                          region,
                          nregion,
                          type_event,
                          lead_status
                 order by email,
                          channel,
                          mth,
                          cmth,
                          region,
                          type_event,
                          lead_status
                Regards

                Etbin
                • 5. Re: Get the MAX(timestamp) and use it on a LEFT OUTER JOIN
                  madmac
                  Thanks to all that took the time to respond to this thread. With your help my query is working correctly now.

                  Edited by: madmac on May 7, 2013 9:54 AM
                  • 6. Re: Get the MAX(timestamp) and use it on a LEFT OUTER JOIN
                    Etbin
                    Beside working correctly is the new query faster too ?
                    A single pass through <tt>tkt_history</tt> to collect all columns needed vs. a separate join for each column needed.
                    Is there any noticeable difference in performance (assuming you're not dealing with just few rows) ?

                    Regards

                    Etbin
                    • 7. Re: Get the MAX(timestamp) and use it on a LEFT OUTER JOIN
                      madmac
                      It rocks. The reason I did all the left outer joins was I did not think of using the group by technique. I'm sure you will help me in the futre. Thanks again.