Home » Developer & Programmer » Reports & Discoverer » dense_rank() in formula column (6i, oracle 10g)
dense_rank() in formula column [message #690198] |
Fri, 29 November 2024 04:47  |
shahzad-ul-hasan
Messages: 643 Registered: August 2002
|
Senior Member |
|
|
i have report with percentage. i want to calculate dense_rank on formula column . My formula column value is
function CF_7Formula return Number is --calculate percentage.
begin
return((nvl(:sumpresent,0)/nvl(:sumwday,0))*100);
end;
Data:
stuid Name Percentage Rank
1 ABC 87.50 want to calculate????
2 FAC 84.20 want to calculate????
3 DAC 85.78 want to calculate????
4 2AC 93.10 want to calculate????
5 HBC 94.58 want to calculate????
6 JA2 79.45 want to calculate????
want to use dense_rank on percentage.
|
|
|
|
|
|
|
Re: dense_rank() in formula column [message #690215 is a reply to message #690206] |
Mon, 09 December 2024 10:07  |
shahzad-ul-hasan
Messages: 643 Registered: August 2002
|
Senior Member |
|
|
please see the attached file.

Query-1
SELECT sno,to_char(mn,'MONTH-YYYY') mm,stmn.class|| ' ' || stmn.section clss,
' '||stmn.name name,
stmn.stuid,
count(astuid) Absents
,substr(last_day(mn),1,2) Totaldays,studentid,wdays,DA
from (select student.class, student.section,
' '||student.name name,
student.stuid,studentid, da,mn
from (select trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 6 +
least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),6) -
least(dt - trunc(dt,'iw'),6) da,add_months(TRUNC(to_Date(to_char(:yea,'DD-MON-YYYY'), 'DD/MM/YYYY')), rownum-1 ) mn
from (SELECT add_months(to_date(TO_CHAR(:yea,'DD-MON-YY'),'DD/MM/YYYY'),level - 1) dt
FROM dual CONNECT BY LEVEL < 13)),STUDENT
where status='PRESENT') stmn,absent1,months
WHERE absent1.astuid(+)=stmn.stuid
and trunc(absent1.abdate(+), 'MON')=stmn.mn
and class=:cls
and section=:sec
and absent1.abdate(+)>=:yea
and to_char(mon,'MON')=to_char(mn,'MON')
and mon>=:yea
group by
sno,stmn.CLASS|| ' ' ||
stmn.SECTION, stmn.NAME, substr(last_day(mn),1,2) ,stmn.stuid,to_char(mn,'MONTH-YYYY'),to_char(mn,'MM'),studentid,wdays,da
order by sno,to_char(mn,'MM')
Query-2
SELECT t.*, rownum FROM
(select pc,dense_rank() over (partition by stuid order by pc desc nulls last) as pos,stuid,class,section
from (select round((nvl(:sumpresent,0)/nvl(:sumwday,0))*100) As pc,stuid,class,section
from student,absent1
where student.stuid=absent1.astuid
and student.stuid=:stuid
and student.status='PRESENT'
group by stuid,class,section)) t
Rep: 1302 cross product has two or more child group.
[Updated on: Mon, 09 December 2024 10:08] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Fri May 02 13:04:02 CDT 2025
|