Calculation for Number of students presents,Absents and overall percentage.

0

hi, 

I have an requirement where my report should be generate the total attendance of the class by selecting start date and end date.

I have created the report where till now it was working fine to generate my report.But,I had got strucked in performing the calculations for total number of presents,absents and over all persentage.

My query is as shown below.

SELECT

AD_COLUMN_IDENTIFIER('enroll_studentdetails1',atd.enroll_studentdetails1_id,'en_US') AS Studentname,
AD_COLUMN_IDENTIFIER('hr_employee',atd.hr_employee_id,'en_US') AS Employee,
AD_COLUMN_IDENTIFIER('setup_course',atd.setup_course_id,'en_US') AS course,
AD_COLUMN_IDENTIFIER('curr_class_date',atd.curr_class_date_id,'en_US') AS classDateID,
atd.curr_attnd_roster_name,atd.curr_attnd_roster_attendance,
 CASE
           WHEN atd.curr_attnd_roster_attendance = 'Present' THEN 'P'
           WHEN atd.curr_attnd_roster_attendance = 'Absent' THEN 'A'
           ELSE 'unknown'
  END AS Attendance,
atd.curr_attnd_roster_reason,CAST(atd.curr_attnd_roster_classdate as DATE) as atdclassdate,CAST(atd.curr_attnd_roster_starttime as DATE) as starttime,
std.enroll_rollno,std.enroll_first_name,CAST(cdate.curr_classdate as DATE) as classdate,atd.setup_course_id

from curr_attnd_roster atd,enroll_studentdetails1 std,setup_course co,curr_class_date cdate

where std.enroll_studentdetails1_id=atd.enroll_studentdetails1_id and co.setup_course_id=atd.setup_course_id
and cdate.curr_class_date_id=atd.curr_class_date_id and $P{Course_ID}=atd.setup_course_id

And my preview should be displayed as shown below,

Rollno          StudentName     30/05/18     01/06/18    02/06/18        03/06/18       Presents        Absents          Persentage

1122               Ram                          P                   A                  P                      P    

1123               Shyam                      P                    P                  P                     P

1124               Srinu                        A                    A                 A                     P

............

............

............

(NOTE:Dates are displayed by selecting classdate from my query.)

I have created the report till the above data as shown,I am unable to perform the calculation for presnt,absent and persentage.

I had created the report using crosstab.

I will be very glad to you if you will be helpfull to get resolve my issue.

Thank's in advance. 

sumithnangunuri's picture
Joined: Nov 9 2017 - 2:41am
Last seen: 1 year 6 months ago

0 Answers:

No answers yet
Feedback