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

# Percentage calculation from a certain point in time

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

AND D1.STATUS = 'PRESENT'

group by d1.id

TABLE1

(ID     VARCHAR2(8),
UNIT    VARCHAR2(8),
STATUS VARCHAR2(8),
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');

 ID UNIT STATUS GRADE YEAR PERIOD 1 FGT458 PRESENT HD 2010 SP4 1 FGT459 PRESENT HD 2011 SP1 1 PREP01 PRESENT P 2011 SP1 1 OPT878 APSENT F 2011 SP2 1 FRE111 PRESENT D 2011 SP3 1 PREP02 PRESENT F 2012 SP1 1 PREP03 PRESENT F 2013 SP4

TABLE2

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');

 GRADE PASS OR BETTER HD Y D Y P Y F N

GIves:

 ID PASS RATE 1 66.7

Want

 ID PASS RATE 1 33.3
• ###### 1. Re: Percentage calculation from a certain point in time

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

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>

1 person found this helpful
• ###### 2. Re: Percentage calculation from a certain point in time

Thanks for you assistance

I found an example which has  me puzzled

using your code on a case such as

 ID UNIT STATUS GRADE YEAR PERIOD 2 SUS108 PRESENT F 2013 SP1 2 PREP01 PRESENT F 2012 SP1 2 SSH100 PRESENT F 2012 SP3 2 COD125 PRESENT F 2012 SP2 2 SOC134 PRESENT D 2012 SP1 2 SGY110 PRESENT F 2011 SP1 2 SOC105 PRESENT F 2012 SP3

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

STUDIED:

 2 2012 SOC105 SP3 OTHER F 2 2013 SUS108 SP1 OTHER F 2 2012 SSH100 SP3 OTHER F 2 2012 COD125 SP2 OTHER F

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

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

 2 SSH100 PRESENT F 2012 SP3 2 COD125 PRESENT F 2012 SP2 2 SOC134 PRESENT D 2012 SP1 2 SGY110 PRESENT F 2011 SP1 2 SOC105 PRESENT F 2012 SP3
• ###### 4. Re: Percentage calculation from a certain point in time

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

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

and a.status = 'PRESENT'

)

where flag = 1

and unit_temp != 'PREP01'

group

by id;

• ###### 6. Re: Percentage calculation from a certain point in time

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

AND d1.status = 'PRESENT'

AND NOT EXISTS (

SELECT 1

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

AND d3.YEAR = d1.YEAR)

GROUP BY d1.ID

1 person found this helpful
• ###### 7. Re: Percentage calculation from a certain point in time

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

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:

 ID UNIT STATUS GRADE YEAR PERIOD 2 PREP01 PRESENT F 2012 SP3

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

Thanks

• ###### 9. Re: Percentage calculation from a certain point in time

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

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

Hello Chole_19

Please see if the following query helps!

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

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

As always thank you Frank