4 Replies Latest reply: Jan 19, 2013 6:51 AM by 985898 RSS

    Combine two Query Results with Rank

    985898
      Dear All,

      I am business analyst working DB for first time:

      I have two queries:
      1)
      select * from (select "CAFNo","ActionDate","ActionDetail", rank() over (partition by "CAFNo" order by "ActionDate") rnk,
      from "ABC"
      where "ActionDetail"
      like 'CRF successfully submitted and routed to Fulfillment Team%')
      where rnk=1

      Results in
      CAFNo","ActionDate1","ActionDetail"

      2)
      select * from (select "CAFNo","ActionDate","ActionDetail", rank() over (partition by "CAFNo" order by "ActionDate") rnk
      from "ABC"
      where "ActionDetail"
      like '%Customer ID%')
      where rnk=1

      Results in
      CAFNo","ActionDate2","ActionDetail"
      ---------------------------------------------------------------------
      I would like to combine results of two as:
      CAF no, Actiondate1, actiondate2,ActionDetail

      I tried full outer join and other options but with no success. Please help
      Please share concept of joining the two results not the actual query.
        • 1. Re: Combine two Query Results with Rank
          Solomon Yakobson
          with t as (
                     select  "CAFNo",
                             "ActionDate",
                             "ActionDetail",
                             case
                               when "ActionDetail" like '%Customer ID%' then 2
                               else 1
                             end weight,
                             rank() over(
                                         partition by "CAFNo",
                                                       case
                                                         when "ActionDetail" like '%Customer ID%' then 2
                                                         else 1
                                                       end
                                         order by "ActionDate"
                                        ) rnk
                       from  "ABC" 
                       where "ActionDetail" like '%Customer ID%'
                          or "ActionDetail" like 'CRF successfully submitted and routed to Fulfillment Team%'
                    )
          select  "CAFNo",
                  max(
                      case weight
                        when 1 then "ActionDate"
                      end
                     ) "ActionDate1",
                  max(
                      case weight
                        when 2 then "ActionDate"
                      end
                     ) "ActionDate2",
                  max(
                      case weight
                        when 1 then "ActionDetail"
                      end
                     ) "ActionDetail1",
                  max(
                      case weight
                        when 2 then "ActionDetail"
                      end
                     ) "ActionDetail2"
            from  t
            where rnk = 1
            group by "CAFNo"
          /
          SY.
          • 2. Re: Combine two Query Results with Rank
            Solomon Yakobson
            Actually, my query is not equivalent to original. I missed ActionDetail can be something like:
            'CRF successfully submitted and routed to Fulfillment Team A by Customer ID X'
            Then it fits both LIKE conditions. If such case of ActionDetail is possible then:
            with t as (
                        select  *
                          from  (
                                 select  1 weight,
                                         "CAFNo",
                                         "ActionDate",
                                         "ActionDetail",
                                         rank() over (partition by "CAFNo" order by "ActionDate") rnk
                                   from  "ABC" 
                                   where "ActionDetail" like 'CRF successfully submitted and routed to Fulfillment Team%'
                                ) 
                          where rnk=1
                       union all
                        select  *
                          from  (
                                 select  2 weight,
                                         "CAFNo",
                                         "ActionDate",
                                         "ActionDetail",
                                         rank() over(partition by "CAFNo" order by "ActionDate") rnk
                                   from  "ABC" 
                                   where "ActionDetail" like '%Customer ID%'
                                ) 
                          where rnk=1
                      )
            select  "CAFNo",
                    max(
                        case weight
                          when 1 then "ActionDate"
                        end
                       ) "ActionDate1",
                    max(
                        case weight
                          when 2 then "ActionDate"
                        end
                       ) "ActionDate2",
                    max(
                        case weight
                          when 1 then "ActionDetail"
                        end
                       ) "ActionDetail1",
                    max(
                        case weight
                          when 2 then "ActionDetail"
                        end
                       ) "ActionDetail2"
              from  t
              group by "CAFNo"
            /
            SY.
            • 3. Re: Combine two Query Results with Rank
              Solomon Yakobson
              982895 wrote:
              I tried full outer join and other options but with no success.
              If you want to use full outer join:
              with t1 as (
                          select  *
                            from  (
                                   select  "CAFNo",
                                           "ActionDate",
                                           "ActionDetail",
                                           rank() over (partition by "CAFNo" order by "ActionDate") rnk
                                     from  "ABC" 
                                     where "ActionDetail" like 'CRF successfully submitted and routed to Fulfillment Team%'
                                  ) 
                            where rnk=1
                         ),
                   t2 as (
                          select  *
                            from  (
                                   select  2 weight,
                                           "CAFNo",
                                           "ActionDate",
                                           "ActionDetail",
                                           rank() over(partition by "CAFNo" order by "ActionDate") rnk
                                     from  "ABC" 
                                     where "ActionDetail" like '%Customer ID%'
                                  ) 
                            where rnk=1
                        )
              select  coalesce(t1."CAFNo",t2."CAFNo") "CAFNo",
                      t1."ActionDate" "ActionDate1",
                      t2."ActionDate" "ActionDate2",
                      t1."ActionDetail" "ActionDetail1",
                      t2."ActionDetail" "ActionDetail2"
                from      t1
                      full join
                          t2
                        on t2."CAFNo" = t1."CAFNo"
              /
              SY.
              • 4. Re: Combine two Query Results with Rank
                985898
                Thanks Solomon .

                It worked, I will go through the concept used in solving the case.


                Regards,
                Nitin Bhardwaj