Calculate Time Difference (Duration) between fields in Oracle Stored Procedure

zeeshanpas
 
on Apr 29, 2022 07:28 AM
872 Views

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?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 02, 2022 01:15 AM

Refer below query.

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,
		GREATEST(TO_DATE(TO_DATE(ses.actual_endtime, 'YYYY-MM-DD') -  TO_DATE(ses.actual_starttime, 'YYYY-MM-DD')),TO_DATE(TO_DATE(ses.scheduled_endtime, 'YYYY-MM-DD') -  TO_DATE(ses.schedule_starttime, 'YYYY-MM-DD'))) AS session_billable_duration
    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;