5 Replies Latest reply on Oct 4, 2013 3:53 PM by Etbin

    Query using analytics function in SQL

    user10089645

      Hello,

      I have a situation here .. kindly guide me..

       

      There are 2 tables, Case table (summary table) and Task table (detailed table)

      The structure of tables:

      Case Table:

      Case_ID,

      1. Trans_CT, à(transaction count could be 1,2,3 ..any numeric  value)

      Case_Rec_ID  (major cols required)

      Task table:

      Case_ID

      Case_Rec_ID,

      Task Name,

      Work_group_name,

      Ready_to_work_in (Yes/No),

      Ready_to_work_ts

      Lst_Upd_ts

      A work group can contain multiple cases.

      I need to find out the # of ready_to_work transactions per case and roll it up to work group level.

      The transaction Count is given at Case level (it is predefined in the source application;need not bother about how the value arrived;) if we do a join between case and task table , trans_ct appears for all the tasks. But there is a business rule for calculating the # of transactions(summation).

      A case contains several tasks like for ex. & corresponding ready to work indicator like below

      Case1 – Task 1   Y

                      Task2    Y

                      Task3    N

                      Task4    Y

                      Task5    Y

      Case2 - Task 1   Y

                      Task2    Y

                      Task3    N

                      Task4    Y

                      Task5    N

      If the RTW indicator is N for the last or recent task within the case then it can be ignored.

      If the RTW indicator is Y for the last or recent task then we need consider the “trans_ct” value as # of RTW transaction for that case.

      So Case 2 shall be ignored.

      In Case 1, you can find the RTW flag has switched from Y-N-Y. If there is a switch of RTW flag within the case from Y to N then to Y and the last or recent tasks RTW flag is Y then we need to account for the number of switches & consider the corresponding “trans_ct” value. (# of switches * trans_ct)

      Let’s say the trans_ct is 3 for Case 1. Now in this scenario, the # of RTW transactions would be (2*3=6 ).

      Now I need to develop a query that can handle this scenario and roll up to work group level.

      Tried the analytic functions lag,lead and first value, last value…but still trying to figure out the logic to handle this case1.

      FYI- The Tasks need to be ordered by last_upd_ts

      Please can you help me with the query.

       

      Thanks

      Vinoth

        • 1. Re: Query using analytics function in SQL
          Karthick2003

          Give us

           

          1. DB Version

          2. Table structure as CREATE TABLE statement.

          3. Sample data as INSERT statement

          4. Expected output.

           

          Also read Re: 2. How do I ask a question on the forums?

          • 2. Re: Query using analytics function in SQL
            Etbin

            Are you after something like this ?

             

            with

            cases as

            (select 1 case_id,3 trans_ct,5 case_rec_id from dual union all

            select 2,4,5 from dual union all

            select 3,1,4 from dual union all

            select 4,2,5 from dual

            ),

            tasks as

            (select 1 case_id,1 case_rec_id,'task 1' task_name,'g 1' work_group_name,'Y' ready_to_work_in,sysdate - 20 ready_to_work_ts,sysdate - 20 lst_upd_ts from dual union all

            select 1,2,'task 2','g 1','Y',sysdate - 20,sysdate - 18 from dual union all

            select 1,3,'task 3','g 1','N',sysdate - 20,sysdate - 17 from dual union all

            select 1,4,'task 4','g 1','Y',sysdate - 16,sysdate - 15 from dual union all

            select 1,5,'task 5','g 1','Y',sysdate - 16,sysdate - 12 from dual union all

            select 2,1,'task 1','g 1','Y',sysdate - 18,sysdate - 18 from dual union all

            select 2,2,'task 2','g 1','Y',sysdate - 16,sysdate - 16 from dual union all

            select 2,3,'task 3','g 1','N',sysdate - 12,sysdate - 12 from dual union all

            select 2,4,'task 4','g 1','Y',sysdate - 11,sysdate - 11 from dual union all

            select 2,5,'task 5','g 1','N',sysdate - 10,sysdate - 10 from dual union all

            select 3,1,'task 3','g 2','Y',sysdate - 13,sysdate - 13 from dual union all

            select 3,2,'task 5','g 2','N',sysdate - 13,sysdate - 12 from dual union all

            select 3,3,'task 1','g 2','N',sysdate - 13,sysdate - 11 from dual union all

            select 3,4,'task 2','g 2','Y',sysdate - 13,sysdate - 10 from dual union all

            select 4,1,'task 1','g 1','Y',sysdate - 20,sysdate - 20 from dual union all

            select 4,2,'task 2','g 1','N',sysdate - 20,sysdate - 17 from dual union all

            select 4,3,'task 3','g 1','Y',sysdate - 15,sysdate - 15 from dual union all

            select 4,4,'task 4','g 1','N',sysdate - 15,sysdate - 13 from dual union all

            select 4,5,'task 5','g 1','Y',sysdate - 15,sysdate - 10 from dual

            ),

            CASE_IDTRANS_CTCASE_REC_ID
            135
            245
            314
            425

             

            CASE_IDCASE_REC_IDTASK_NAMEWORK_GROUP_NAMEREADY_TO_WORK_INREADY_TO_WORK_TSLST_UPD_TS
            11task 1g 1Y09/13/201309/13/2013
            12task 2g 1Y09/13/201309/15/2013
            13task 3g 1N09/13/201309/16/2013
            14task 4g 1Y09/17/201309/18/2013
            15task 5g 1Y09/17/201309/21/2013
            21task 1g 1Y09/15/201309/15/2013
            22task 2g 1Y09/17/201309/17/2013
            23task 3g 1N09/21/201309/21/2013
            24task 4g 1Y09/22/201309/22/2013
            25task 5g 1N09/23/201309/23/2013
            31task 3g 2Y09/20/201309/20/2013
            32task 5g 2N09/20/201309/21/2013
            33task 1g 2N09/20/201309/22/2013
            34task 2g 2Y09/20/201309/23/2013
            41task 1g 1Y09/13/201309/13/2013
            42task 2g 1N09/13/201309/16/2013
            43task 3g 1Y09/18/201309/18/2013
            44task 4g 1N09/18/201309/20/2013
            45task 5g 1Y09/18/201309/23/2013

             

            switches as

            (select case_id,case_rec_id,task_name,work_group_name,ready_to_work_in,ready_to_work_ts,lst_upd_ts,switch

               from (select case_id,case_rec_id,task_name,work_group_name,ready_to_work_in,ready_to_work_ts,lst_upd_ts,

                            case when ready_to_work_in = 'N'

                                  and lag(ready_to_work_in) over (partition by case_id order by lst_upd_ts) = 'Y'

                                  and lead(ready_to_work_in) over (partition by case_id order by lst_upd_ts) = 'Y'

                                 then 2

                            end switch,

                            first_value(ready_to_work_in) over (partition by case_id order by lst_upd_ts desc) last_rtw

                       from tasks

                    )

              where last_rtw = 'Y'

            )

            CASE_IDCASE_REC_IDTASK_NAMEWORK_GROUP_NAMEREADY_TO_WORK_INREADY_TO_WORK_TSLST_UPD_TSSWITCH
            11task 1g 1Y09/13/201309/13/2013-
            12task 2g 1Y09/13/201309/15/2013-
            13task 3g 1N09/13/201309/16/20132
            14task 4g 1Y09/17/201309/18/2013-
            15task 5g 1Y09/17/201309/21/2013-
            31task 3g 2Y09/20/201309/20/2013-
            32task 5g 2N09/20/201309/21/2013-
            33task 1g 2N09/20/201309/22/2013-
            34task 2g 2Y09/20/201309/23/2013-
            41task 1g 1Y09/13/201309/13/2013-
            42task 2g 1N09/13/201309/16/20132
            43task 3g 1Y09/18/201309/18/2013-
            44task 4g 1N09/18/201309/20/20132
            45task 5g 1Y09/18/201309/23/2013-

             

            select c.case_id,c.trans_ct,s.switch_sum,c.trans_ct * nvl(s.switch_sum,1) rtw_transactions

              from cases c,

                   (select case_id,sum(switch) switch_sum

                      from switches

                     group by case_id

                   ) s

            where s.case_id = c.case_id

            order by case_id

             

            CASE_IDTRANS_CTSWITCH_SUMRTW_TRANSACTIONS
            1326
            4248
            31-1

             

            Regards

             

            Etbin

            • 3. Re: Query using analytics function in SQL
              user10089645

              Absolutely this should help a lot Etbin...Thanks for your detailed inputs.Very much helpful.

              • 4. Re: Query using analytics function in SQL
                user10089645

                Hi,

                This was helpful. But every first 'Y' occurence has to be accounted within a case(occurence after switch within a case included). So in the example I have modified the switch column data (which i would otherwise call count of RTW flag column),

                 

                CASE_IDCASE_REC_IDTASK_NAMEWORK_GROUP_NAMEREADY_TO_WORK_INREADY_TO_WORK_TSLST_UPD_TSSWITCH
                11task 1g 1Y09/13/201309/13/20131
                12task 2g 1Y09/13/201309/15/2013-
                13task 3g 1N09/13/201309/16/2013
                14task 4g 1Y09/17/201309/18/20131
                15task 5g 1Y09/17/201309/21/2013-
                31task 3g 2Y09/20/201309/20/20131
                32task 5g 2N09/20/201309/21/2013-
                33task 1g 2N09/20/201309/22/2013-
                34task 2g 2Y09/20/201309/23/20131
                41task 1g 1Y09/13/201309/13/20131
                42task 2g 1N09/13/201309/16/2013
                43task 3g 1Y09/18/201309/18/20131
                44task 4g 1N09/18/201309/20/2013
                45task 5g 1Y09/18/201309/23/20131

                 

                Thanks

                Vinoth

                • 5. Re: Query using analytics function in SQL
                  Etbin

                  I'm not sure if you expect the code or not


                  switches as

                  (select case_id,case_rec_id,task_name,work_group_name,ready_to_work_in,ready_to_work_ts,lst_upd_ts,switch

                     from (select case_id,case_rec_id,task_name,work_group_name,ready_to_work_in,ready_to_work_ts,lst_upd_ts,

                                  case when ready_to_work_in = 'Y'

                                        and lag(ready_to_work_in) over (partition by case_id order by lst_upd_ts) = 'N'

                                       then 1

                                       when row_number() over (partition by case_id order by lst_upd_ts) = 1

                                        and ready_to_work_in = 'Y'

                                       then 1

                                  end switch,

                                  first_value(ready_to_work_in) over (partition by case_id order by lst_upd_ts desc) last_rtw

                             from tasks

                          )

                    where last_rtw = 'Y'

                  )

                   

                  CASE_IDCASE_REC_IDTASK_NAMEWORK_GROUP_NAMEREADY_TO_WORK_INREADY_TO_WORK_TSLST_UPD_TSSWITCH
                  11task 1g 1Y09/14/201309/14/20131
                  12task 2g 1Y09/14/201309/16/2013-
                  13task 3g 1N09/14/201309/17/2013-
                  14task 4g 1Y09/18/201309/19/20131
                  15task 5g 1Y09/18/201309/22/2013-
                  31task 3g 2Y09/21/201309/21/20131
                  32task 5g 2N09/21/201309/22/2013-
                  33task 1g 2N09/21/201309/23/2013-
                  34task 2g 2Y09/21/201309/24/20131
                  41task 1g 1Y09/14/201309/14/20131
                  42task 2g 1N09/14/201309/17/2013-
                  43task 3g 1Y09/19/201309/19/20131
                  44task 4g 1N09/19/201309/21/2013-
                  45task 5g 1Y09/19/201309/24/20131

                   

                  Regards

                   

                  Etbin