This discussion is archived
11 Replies Latest reply: Jun 17, 2013 4:20 PM by Chloe_19 RSS

Percentage calculation from a certain point in time

Chloe_19 Newbie
Currently Being Moderated

I am trying to calculate the pass rate from after the ID studied a particular UNIT.

So I want to get the rate AFTER they studied their PREP01 unit

So right now  the code below works fine but it considers all units.

 

ID 1 studied one UNIT in 2010 which is before PREP so I do not want to consider it.

Also studied FGT459 in 2011 SP 1 as well as their FIRST PREP unit in SP 1 so i DO NOT want to consider it. IF A UNIT HAS BEEN DONE AT THE SAME TIME AS THE PREP01 UNIT I WOULD LIKE TO IGNORE IT.

After the PREP unit i want to consider any unit which has the Status as PRESENT. so out of the 3 remaning units they passed 1 and failed 3 so the percentage pass is 33.3%

Right now my code gives me 66.7% coz i condier all units.

I would only like to consider after PREP has been studied (PRESENT) IF A UNIT HAS BEEN DONE AT THE SAME TIME AS THE PREP01 UNIT I WOULD LIKE TO IGNORE IT.

 

Thanks for any assistance.

 

The code I tried is:

 

select  d1.id, ROUND(avg(case PASS_OR_BETTER

              when 'Y' then 1

              else          0

              end ) * 100,1)  as Pass

FROM DAN_SPIL_GRADE_IDS D1, DAN_SPIL_GRADE D2

WHERE D1.GRADE = D2.GRADE

AND D1.STATUS = 'PRESENT'

group by d1.id

 

 

 

TABLE1


CREATE TABLE DAN_SPIL_GRADE_IDS
(ID     VARCHAR2(8),
UNIT    VARCHAR2(8),
STATUS VARCHAR2(8),
GRADE VARCHAR2(12),
YEAR VARCHAR2(12),
PERIOD VARCHAR2(12));

 

INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,STATUS,GRADE,YEAR,PERIOD) VALUES ('1','FGT458','PRESENT','HD','2010','SP4');
INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,STATUS,GRADE,YEAR,PERIOD) VALUES ('1','FGT459','PRESENT','HD','2011','SP1');
INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,STATUS,GRADE,YEAR,PERIOD) VALUES ('1','PREP01','PRESENT','P','2011','SP1');
INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,STATUS,GRADE,YEAR,PERIOD) VALUES ('1','OPT878','ABSENT' ,'F','2011','SP2');
INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,STATUS,GRADE,YEAR,PERIOD) VALUES ('1','FRE111','PRESENT','D','2011','SP3');
INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,STATUS,GRADE,YEAR,PERIOD) VALUES ('1','PREP02','PRESENT','F','2012','SP1');
INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,STATUS,GRADE,YEAR,PERIOD) VALUES ('1','PREP03','PRESENT','F','2013','SP4');

 

IDUNITSTATUSGRADEYEARPERIOD
1FGT458PRESENTHD2010SP4
1FGT459PRESENTHD2011SP1
1PREP01PRESENTP2011SP1
1OPT878APSENTF2011SP2
1FRE111PRESENTD2011SP3
1PREP02PRESENTF2012SP1
1PREP03PRESENTF2013SP4

 

 

TABLE2


CREATE TABLE DAN_SPIL_GRADE
(GRADE     VARCHAR2(8),
PASS_OR_BETTER VARCHAR2(12));

INSERT INTO DAN_SPIL_GRADE (GRADE, PASS_OR_BETTER) VALUES ('HD','Y');
INSERT INTO DAN_SPIL_GRADE (GRADE, PASS_OR_BETTER) VALUES ('D','Y');
INSERT INTO DAN_SPIL_GRADE (GRADE, PASS_OR_BETTER) VALUES ('P','Y');
INSERT INTO DAN_SPIL_GRADE (GRADE, PASS_OR_BETTER) VALUES ('F','N');

 

GRADEPASS OR BETTER
HDY
DY
PY
FN

 

GIves:

 

IDPASS RATE
166.7

 

Want

 

IDPASS RATE
133.3
  • 1. Re: Percentage calculation from a certain point in time
    Karthick_Arp Guru
    Currently Being Moderated

    Like this

     

    SQL> select id

      2       , round

      3         (

      4            avg

      5            (

      6               case pass_or_better when 'Y' then 1 else 0 end

      7            ) * 100

      8          , 1

      9         )  as pass

    10    from (

    11            select a.id

    12                 , a.unit

    13                 , b.pass_or_better

    14                 , a.year

    15                 , a.period

    16                 , max(decode(unit, 'PREP01', 1, 0)) over(partition by a.id order by year, period, unit) flag

    17              from dan_spil_grade_ids a

    18              join dan_spil_grade b

    19                on a.grade = b.grade

    20               and a.status = 'PRESENT'

    21         )

    22   where flag = 1

    23     and unit != 'PREP01'

    24   group

    25      by id

    26  /

     

    ID             PASS

    -------- ----------

    1              33.3

     

    SQL>

  • 2. Re: Percentage calculation from a certain point in time
    Chloe_19 Newbie
    Currently Being Moderated

    Thanks for you assistance

     

    I found an example which has  me puzzled

     

    using your code on a case such as

     

    IDUNITSTATUSGRADEYEARPERIOD
    2SUS108PRESENTF2013SP1
    2PREP01PRESENTF2012SP1
    2SSH100PRESENTF2012SP3
    2COD125PRESENTF2012SP2
    2SOC134PRESENTD2012SP1
    2SGY110PRESENTF2011SP1
    2SOC105PRESENTF2012SP3

     

    Where ID 2 studied 4 units after PREP01 in 2012 SP 1

     

    STUDIED:

     

    22012SOC105SP3OTHERF
    22013SUS108SP1OTHERF
    22012SSH100SP3OTHERF
    22012COD125SP2OTHERF

     

    Your code gives 20% when it should give 0 because after PREP01 2012 SP1 the ID Failed everything

  • 3. Re: Percentage calculation from a certain point in time
    Karthick_Arp Guru
    Currently Being Moderated

    Should it not be 5 records? Why are you ignoring SOC134

     

    2SSH100PRESENTF2012SP3
    2COD125PRESENTF2012SP2
    2SOC134PRESENTD2012SP1
    2SGY110PRESENTF2011SP1
    2SOC105PRESENTF2012SP3
  • 4. Re: Percentage calculation from a certain point in time
    Chloe_19 Newbie
    Currently Being Moderated

    Because it was at the same time as the PREP unit same year and SP so it should be ignored

  • 5. Re: Percentage calculation from a certain point in time
    Karthick_Arp Guru
    Currently Being Moderated

    Try this

     

    select id

         , round

           (

              avg

              (

                 case pass_or_better when 'Y' then 1 else 0 end

              ) * 100

            , 1

           )  as pass

      from ( 

              select a.id

                   , a.unit

                   , b.pass_or_better

                   , a.year

                   , a.period

                   , max(decode(unit, 'PREP01', 1, 0)) over(partition by a.id order by year, period, unit) flag

                   , max(unit) keep(dense_rank first order by decode(unit, 'PREP01', 0, 1)) over(partition by id, year, period) unit_temp

                from dan_spil_grade_ids a

                join dan_spil_grade b

                  on a.grade = b.grade

                 and a.status = 'PRESENT'

           )

    where flag = 1

       and unit_temp != 'PREP01'

    group

        by id;

  • 6. Re: Percentage calculation from a certain point in time
    VarunNagaraja Newbie
    Currently Being Moderated

    Hi Chloe, refer to the below SQL

     

    SELECT   d1.ID,

             ROUND (AVG (CASE pass_or_better

                            WHEN 'Y'

                               THEN 1

                            ELSE 0

                         END) * 100, 1) AS pass

        FROM dan_spil_grade_ids d1, dan_spil_grade d2

       WHERE d1.grade = d2.grade

         AND d1.status = 'PRESENT'

         AND NOT EXISTS (

                  SELECT 1

                    FROM dan_spil_grade_ids d3

                   WHERE d3.unit = 'PREP01' AND d3.ID = d1.ID

                         AND d3.YEAR = d1.YEAR)

    GROUP BY d1.ID

  • 7. Re: Percentage calculation from a certain point in time
    Chloe_19 Newbie
    Currently Being Moderated


    Thank you very much Karthick

     

    The code gives me the correct percentage and count correctly after PREP ingornint anything that was done at the same time.

     

    However some ID are being left out, not sure why. Am you trying to undertand you code how it works and why those IDs are not displaying.

  • 8. Re: Percentage calculation from a certain point in time
    Chloe_19 Newbie
    Currently Being Moderated

    I realised the code gives me all the IDs given they have enrolments to be counted after PREP01

     

    However if PREP01 was only studied or PREP01 and mulitple other units at the same study period the IDS get ignored all together.

     

    example:

    IDUNITSTATUSGRADEYEARPERIOD
    2PREP01PRESENTF2012SP3

     

    How could I place null or '0' instead of ignoring the IDs?

     

    Thanks

     


  • 9. Re: Percentage calculation from a certain point in time
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    A condition in a WHERE clause can cause an entire row to be ignored.

    The same condition in a CASE expression can cause individual values to be ignored, while keeping the row.

    You can modify Karthick's solution, take the comparison of unit_temp to 'PREP01' out of the WHERE clause, and put it in the CASE expression, like this:

     

    select id

         , round

           (

              avg

              (

                 case

                     when  unit_temp = 'PREP01'  then NULL -- compare here

                     when  pass_or_better = 'Y'  then 100

                                                 else 0

                 end

              )

            , 1

           )  as pass

      from (

              select a.id

                   , a.unit

                   , b.pass_or_better

                   , a.year

                   , a.period

                   , max(decode(unit, 'PREP01', 1, 0)) over(partition by a.id order by year, period, unit) flag

                   , max(unit) keep(dense_rank first order by decode(unit, 'PREP01', 0, 1)) over(partition by id, year, period) unit_temp

                from dan_spil_grade_ids a

                join dan_spil_grade b

                  on a.grade = b.grade

                 and a.status = 'PRESENT'

           )

    where flag = 1

    --   and unit_temp != 'PREP01'  -- not here

    group

        by id;

     

    The output from your sample data with this row added:

     

    INSERT INTO DAN_SPIL_GRADE_IDS (ID, UNIT,     STATUS,    GRADE, YEAR,   PERIOD)

                            VALUES ('2','PREP01', 'PRESENT', 'F',   '2012', 'SP3');

     

     

    is

     

    ID             PASS

    -------- ----------

    1              33.3

    2

  • 10. Re: Percentage calculation from a certain point in time
    tinku981 Newbie
    Currently Being Moderated

    Hello Chole_19

     

    Please see if the following query helps!

     

    select round(100/count(*), 2)

      from DAN_SPIL_GRADE_IDS d1

    where d1.status = 'PRESENT'

       AND (YEAR || PERIOD) > all

    (SELECT YEAR || PERIOD FROM DAN_SPIL_GRADE_IDS WHERE UNIT = 'PREP01');

     

    Cheers

    Tinku

  • 11. Re: Percentage calculation from a certain point in time
    Chloe_19 Newbie
    Currently Being Moderated

    As always thank you Frank

Legend

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