This discussion is archived
10 Replies Latest reply: Mar 4, 2013 11:29 PM by ryansun RSS

Query to get counts and % by date range

ryansun Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    for percenage use Ratio_to_Report
  • 2. Re: Query to get counts and % by date range
    jeneesh Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Chris. This works.

    Thanks for taking the time out for this.

    Ryan.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points