11 Replies Latest reply: Jun 17, 2013 6:20 PM by Chloe_19 RSS

    Percentage calculation from a certain point in time

    Chloe_19

      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

          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

            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

              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

                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

                  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

                    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


                      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

                        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

                          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

                            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

                              As always thank you Frank