0

I would like my report to display something image like as below enter image description here

so can i know is it how my sql for table has to be when creating it

    create table attendance
    (
       Student_ID VARCHAR2 (15) primary key,
       Student_Name VARCHAR2 (50) not null,
       "Date1" VARCHAR2 (15) not null,
       "Date2" VARCHAR2 (15) not null,
       "Date3" VARCHAR2 (15) not null,
       "Date4" VARCHAR2 (15) not null,
       "Date5" VARCHAR2 (15) not null,
       Present number (10),
       Late number (10),
       Absent number (10),
       Total_Attendance number (10)
    );

for present, late, absent and total attendance i will be using like countif but this i have to research on it how to use in oracle. I would like to know is this correct the way it should be used for sql in creating table so that i will get a report the looks as in the picture

0

You can "translate" the appropriate text values to 1 or 0, then add them.

select
  student_id,
  student_name,
  date1,
  date2,
  date3,
  date4,
  date5,
  decode(date1, 'Present', 1, 0) +
  decode(date2, 'Present', 1, 0) +
  decode(date3, 'Present', 1, 0) +
  decode(date4, 'Present', 1, 0) +
  decode(date5, 'Present', 1, 0) as Present,
  decode(date1, 'Late', 1, 0) +
  decode(date2, 'Late', 1, 0) +
  decode(date3, 'Late', 1, 0) +
  decode(date4, 'Late', 1, 0) +
  decode(date5, 'Late', 1, 0) as Late,
  decode(date1, 'Absent', 1, 0) +
  decode(date2, 'Absent', 1, 0) +
  decode(date3, 'Absent', 1, 0) +
  decode(date4, 'Absent', 1, 0) +
  decode(date5, 'Absent', 1, 0) as Present,
  decode(date1, 'Present', 1, 'Late', 1, 0) +
  decode(date2, 'Present', 1, 'Late', 1, 0) +
  decode(date3, 'Present', 1, 'Late', 1, 0) +
  decode(date4, 'Present', 1, 'Late', 1, 0) +
  decode(date5, 'Present', 1, 'Late', 1, 0) as Total_attendance
from
  attendance;

CASE also works, but that is a bit longer:

...
case when date1 = 'Present' then 1 else 0 end +
case when date2 = 'Present' then 1 else 0 end +
case when date3 = 'Present' then 1 else 0 end +
case when date4 = 'Present' then 1 else 0 end +
case when date5 = 'Present' then 1 else 0 end as Present,
...
  • thanks sir for the reply u gave me a good idea .. sir so can i ask the structure of create table i use is correct? like if use such structure i will be able to get report something like shown in the image. – Software Engineer Feb 28 '16 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.