On demand session billable duration = actual duration schedule session billable duration = Max(scheduled duration, actual duration)
Note:
Actual duration = Actual_endtime - Actual_starttime
schedule duration = Schedule_endtime - Schedule_starttime
How do i write this in oracle procedure could you please help me in that ?
PROCEDURE getmiskptireportdata (
istartdate IN VARCHAR,
ienddate IN VARCHAR,
ocursor OUT t_cursor
)
IS
BEGIN
OPEN ocursor FOR
SELECT
ses.session_id,
( ss.participant_status ) status,
nvl(
tf.lesson_objective,
ses.lesson_objective
) lesson_objective,
ses.question,
TO_CHAR(
new_time(
ses.scheduled_starttime,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
) scheduled_starttime,
DECODE(
ss.participant_status,
0,
'Created',
1,
'On-Goining',
2,
'Completed',
3,
'Student Canceled',
4,
'Student Missed',
5,
'Tutor Missed',
6,
'Admin Delete',
7,
'Admin Cancel',
9,
'Orientation Completed',
10,
'Completed - Unsuccessful',
'NULL'
) session_status,
DECODE(
nvl(
ses.session_type,
0
),
1,
'Group',
'Single'
) session_type,
DECODE(
ses.type,
0,
'On-Demand',
1,
'Admin Schedule',
2,
'Student SCHEDULED',
'NA'
) type,
ses.subject_id,
ses.subject_name,
stugrade.grade_name AS studentgrade,
ss.student_id,
st.referrence_id AS studentreferenceid,
st.first_name AS studentfirstname,
st.middle_name AS studentmiddlename,
st.last_name AS studentlastname,
stul.user_name AS studentusername,
TO_CHAR(
new_time(
ses.actual_endtime,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
) AS actual_endtime,
tut.tutor_id,
tut.first_name AS tutorfirstname,
tut.last_name AS tutorlastname,
tut.tutoring_center,
tutl.user_name AS tutorusername,
ses.fqst_actual_time,
ses.school_id,
sc.name AS schoolname,
ss.student_wait_time,
ses.tutor_activity_avg_res_time,
ses.session_question_loaded_time,
sf.session_rating,
sf.tutor_rating,
di.district_id,
di.district_name,
nvl(
TO_CHAR(
new_time(
ses.actual_starttime,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
),
TO_CHAR(
new_time(
ses.scheduled_starttime,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
)
) AS starttime,
nvl(
TO_CHAR(
new_time(
ses.actual_endtime,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
),
TO_CHAR(
new_time(
ses.scheduled_endtime,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
)
) AS endtime,
TO_CHAR(
new_time(
ss.joined_time,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
) AS studentjointime,
TO_CHAR(
new_time(
ss.quit_time,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
) AS studentquittime,
nvl(
sc.is_test_data,
0
) AS istestsession,
tf.participation_points,
tf.progress_notes,
tf.tutor_comments,
round(
(nvl(
ses.actual_endtime,
ses.scheduled_endtime
) - nvl(
ses.actual_starttime,
ses.scheduled_starttime
) ) * 24 * 60,
4
) AS actualduration,
round(
(ses.scheduled_endtime - ses.scheduled_starttime) * 24 * 60,
4
) AS scheduleduration,
ses.billable_duration,
ses.recording_url,
ses.chat_script_url,
( fpg.name ) program_name,
pgv.version_name,
( sf.comments ) student_comments,
fevusr.first_name || ' ' || fevusr.last_name AS TEACHERNAME,
sta.state_name AS STATE,
ss.device AS Device_Type,
ss.browser AS Browser_Type,
decode(ses.schedule_selection_type,0,'Course Work',1,'My Learning Plan',
2,'Orientations',3,'Booster',4,
'Curriculum Help',5,'ETI')Category,
fevsesstag.value AS Curriculum,
ses.program_id,
tf.exit_ticket_attempted AS exit_ticket_attempted,
tf.exit_ticket_earned AS exit_ticket_earned,
tf.exit_ticket_score AS exit_ticket_score,
tf.assessment_readiness AS AssessmentReady
FROM
fev_oltsession ses
LEFT OUTER JOIN fev_student_oltsession ss ON ses.session_id = ss.session_id
LEFT OUTER JOIN fev_school sc ON ses.school_id = sc.school_id
LEFT OUTER JOIN fev_district di ON sc.district_id = di.district_id
LEFT OUTER JOIN fev_oltsession_tutor_feedback tf ON (
ses.session_id = tf.session_id
AND
ss.student_id = tf.student_id
)
LEFT OUTER JOIN fev_oltsesson_student_feedback sf ON (
ses.session_id = sf.session_id
AND
ss.student_id = sf.student_id
)
LEFT OUTER JOIN fev_student st ON ss.student_id = st.student_id
LEFT OUTER JOIN fev_state sta ON st.state_id = sta.state_id
LEFT OUTER JOIN fev_login stul ON st.login_id = stul.login_id
LEFT OUTER JOIN fev_student_teacher studttcher ON st.student_id = studttcher.student_id
AND studttcher.status = 1
LEFT OUTER JOIN fev_user fevusr ON fevusr.user_id = studttcher.teacher_id
LEFT OUTER JOIN fev_session_tag_detail fevsesstag ON fevsesstag.session_id = ses.session_id
LEFT OUTER JOIN fev_tutor_oltsession ts ON (
ses.session_id = ts.session_id
AND
ts.transfered_from IS NULL
)
LEFT OUTER JOIN fev_tutor tut ON ts.tutor_id = tut.tutor_id
LEFT OUTER JOIN fev_login tutl ON tut.login_id = tutl.login_id
LEFT OUTER JOIN fev_grade stugrade ON st.grade_id = stugrade.grade_id
LEFT OUTER JOIN fev_program fpg ON (
fpg.program_id = ses.program_id
)
LEFT OUTER JOIN fev_program_version pgv ON (
pgv.version_id = ses.version_id
)
WHERE
ses.scheduled_starttime >= TO_DATE(
istartdate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
ses.scheduled_endtime < TO_DATE(
ienddate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
nvl(
sc.is_test_data,
0
) != 1
AND
ses.school_id NOT IN (
213,358
)
AND
ses.status IS NOT NULL;
END;
Above is my procedure i already calculated the Actual duration and schedule duration and i need to pass that in the gratest function and to calculate maximum of that how do i acheive that?
what you given i tried that in select query but i dont know how do i do that in procedure so could you please edit my procedure and share it to me it will be really helpfull?
