10 Replies Latest reply: Mar 5, 2013 1:29 AM by Ryansun-Oracle RSS

    Query to get counts and % by date range

    Ryansun-Oracle
      Version 11g.

      Hi There,

      I have the following data in the format
      Opty_Id        creation_date               user          accepted_flag 
      
      1         01-mar-2013                  ryan                  N
      2         02-mar-2013                  sam                   Y
      3         02-mar-2013                  ryan                   Y
      .
      .
      .
      I want to get all the counts and % of all the users who have accepted_flag set to N. Basically, users who have not accepted Opty_id yet. in the format
      ageing             count        Percentage
      0-20 days          1                 10%(this will offcourse depend on the total number of opty id and then out of that how many did not accept within the given period.
      21-40 days        0                  0%
      41-60 days        0                  0%
      The ageing column is creation_date, basically, how many opty id, were created between today and 20 days, 21-40 days and 41 to 60 days and were not yet accepted (accept flag = N)


      Any help please.

      thanks,
      Ryan
        • 1. Re: Query to get counts and % by date range
          Rahul_India
          for percenage use Ratio_to_Report
          • 2. Re: Query to get counts and % by date range
            jeneesh
            You can use analytic function..

            Provide sample data (CREATE TABLE and INSERT statements) and expected output..

            {message:id=9360002}
            • 3. Re: Query to get counts and % by date range
              Ryansun-Oracle
              Hi Jeenesh,


              Sure,
              with t as (select 1 as opty_id, to_date('01/03/2013','DD/MM/YYYY') as date_created, 'ryan' as user, 'N'  as accepted_flag from dual union all
                         select 2, to_date('02/03/2013','DD/MM/YYYY'), 'sun', 'Y' from dual union all
                         select 3, to_date('03/02/2013','DD/MM/YYYY'), 'sun', 'Y' from dual union all
                         select 4, to_date('02/01/2013','DD/MM/YYYY'), 'ryan', 'N' from dual union all
                         select 5, to_date('03/01/2013','DD/MM/YYYY'), 'tom', 'Y' from dual)
                  
              Output are you looking for anything specific other than what I have mentioned in my original question?

              Thanks for helping.
              • 4. Re: Query to get counts and % by date range
                jeneesh
                What will be the expected output from this data?

                How are you getting percentage as 10?
                • 5. Re: Query to get counts and % by date range
                  chris227
                  May be
                  with t as (select 1 as opty_id, to_date('01/03/2013','DD/MM/YYYY') as date_created, 'ryan' as auser, 'N'  as accepted_flag from dual union all
                             select 2, to_date('02/03/2013','DD/MM/YYYY'), 'sun', 'Y' from dual union all
                             select 3, to_date('03/02/2013','DD/MM/YYYY'), 'sun', 'Y' from dual union all
                             select 4, to_date('02/01/2013','DD/MM/YYYY'), 'ryan', 'N' from dual union all
                             select 4, trunc(sysdate),'ryan', 'N' from dual union all
                             select 5, to_date('03/01/2013','DD/MM/YYYY'), 'tom', 'Y' from dual)
                  
                  select
                   decode(grp,1,0,grp*20-19)
                   ||'-'||
                   grp*20
                   ageing
                  ,sum(case accepted_flag when 'Y' then 1 end) count
                  ,sum(case accepted_flag when 'Y' then 1 end)
                  /count(*)*100 Percentage
                  ,count(*)
                  from
                  (select
                   ceil(decode((trunc(sysdate) - date_created),0,1,trunc(sysdate) - date_created)/20) grp
                  ,date_created
                  ,accepted_flag
                  from  t
                  )
                  group by
                  grp
                  
                  AGEING     COUNT     PERCENTAGE     COUNT(*)
                  "0-20"     "1"     "33,33333333333333333333333333333333333333"     "3"
                  "21-40"     "1"     "100"     "1"
                  "41-60"     "1"     "100"     "1"
                  "61-80"               "1"
                  • 6. Re: Query to get counts and % by date range
                    Ryansun-Oracle
                    Hi Chris, Jeenesh,

                    Just one small change. We can further filter out the accepted_flag = 'Y'. I think that we can just add in the where clause. I just want to get the details where accepted_flag = 'N'

                    so the 0-20 would have the counts for all opty_id having accepted_flag ='N' where the opty _Create date is between 0-20 days
                    the % would be the number of opty_id with accept_flag =' N' and where create date between 0-20 divide by total opty_id where accept flag = 'N'

                    so in the data provided, there are only two opty_id with accept_flag = 'N' the output would
                    ageing     count      % of unaccepted Opty  
                    0-20        1            50%
                    21-40      0             0%
                    41-60      1             50%
                    Thanks!
                    • 7. Re: Query to get counts and % by date range
                      chris227
                      I get other results
                      with t as (select 1 as opty_id, to_date('01/03/2013','DD/MM/YYYY') as date_created, 'ryan' as auser, 'N'  as accepted_flag from dual union all
                                 select 2, to_date('02/03/2013','DD/MM/YYYY'), 'sun', 'Y' from dual union all
                                 select 3, to_date('03/02/2013','DD/MM/YYYY'), 'sun', 'Y' from dual union all
                                 select 4, to_date('02/01/2013','DD/MM/YYYY'), 'ryan', 'N' from dual union all
                                 select 5, to_date('03/01/2013','DD/MM/YYYY'), 'tom', 'Y' from dual)
                       
                       
                      select
                       decode(grp,1,0,grp*20-19)
                       ||'-'||
                       grp*20
                       ageing
                      ,sum(case accepted_flag when 'N' then 1 else 0 end) count
                      ,sum(case accepted_flag when 'N' then 1 else 0 end)
                      /sum(sum(case accepted_flag when 'N' then 1 else 0 end)) over () Percentage
                      from
                      (select
                       ceil(decode((trunc(sysdate) - date_created),0,1,trunc(sysdate) - date_created)/20) grp
                      ,date_created
                      ,accepted_flag
                      from  t
                      )
                      group by
                      grp
                      
                      AGEING     COUNT     PERCENTAGE
                      "0-20"     "1"     "0,5"
                      "21-40"     "0"     "0"
                      "41-60"     "0"     "0"
                      "61-80"     "1"     "0,5"
                      Edited by: chris227 on 04.03.2013 03:41
                      Altered based on answer below
                      • 8. Re: Query to get counts and % by date range
                        Ryansun-Oracle
                        Hi Chris,

                        If there are two rows in non accepted status, spread across two periods, it should be 50% each right? Is it because, the count(*) is also counting the ones where accept_status = 'Y'?

                        Thanks!
                        • 9. Re: Query to get counts and % by date range
                          chris227
                          ryansun wrote:
                          Hi Chris,

                          If there are two rows in non accepted status, spread across two periods, it should be 50% each right? Is it because, the count(*) is also counting the ones where accept_status = 'Y'?
                          Yes, i altered the solution above by calculating the percentage of the number of 'N' of the overall number of 'N'.
                          • 10. Re: Query to get counts and % by date range
                            Ryansun-Oracle
                            Thanks Chris. This works.

                            Thanks for taking the time out for this.

                            Ryan.