Calculate difference between start time and end time in Oracle query

zeeshanpas
 
on May 09, 2022 05:27 AM
1380 Views

On demand session billable duration = actual duration schedule session billable duration = Max(scheduled duration, actual duration) For group sessions also the same logic will holds good, but calculation should be per student based on his/her attendance .

actual duration = Actualend time - Actual start time

scheduled duration = scheduled end time - scheduled start time

How do i execute this above logic below query i tried given in the procedure above could you please help me in that

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,
        GETCS_TEACHERNAMESOFSTUDENT(ss.student_id) 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,
     (select value from fev_session_tag_detail where session_id=ses.session_id and lower(key)='curriculum') Curriculum,
      -- 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,
     CASE
     WHEN (ses.type = 0 and ses.session_type=1)--single session
     THEN  round(
            (nvl(
                ses.actual_endtime,
                ses.scheduled_endtime
            ) - nvl(
                ses.actual_starttime,
                ses.scheduled_starttime
            ) ) * 24 * 60,
            4
        ) 'hours'
            WHEN ((ses.type = 1 OR ses.type = 2) and ses.session_type=1 and ss.student_id = sf.student_id)--single session
     THEN GREATEST(round((ses.actual_endtime -  ses.actual_starttime)* 24 * 60,
            4),round((ses.scheduled_endtime -  ses.scheduled_starttime)* 24 * 60,
            4))'hours'
                WHEN (ses.type = 0  and ses.session_type=0)--group session
     THEN round(
            (nvl(
                ses.actual_endtime,
                ses.scheduled_endtime
            ) - nvl(
                ses.actual_starttime,
                ses.scheduled_starttime
            ) ) * 24 * 60,
            4
        )'hours'
           
            ELSE  
             
            WHEN ((ses.type = 1 OR ses.type = 2) and ses.session_type=0 and ss.student_id = sf.student_id)--group session
     THEN GREATEST(round((ses.actual_endtime -  ses.actual_starttime)* 24 * 60,
            4),round((ses.scheduled_endtime -  ses.scheduled_starttime)* 24 * 60,
            4)) END As hours
        
        
    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  and lower(fevsesstag.key)= 'curriculum')
        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(
                '04/09/2022 04:00:00 AM',
                'mm/dd/yyyy hh:mi:ss am'
            )
        AND
            ses.scheduled_endtime < TO_DATE(
                '05/09/2022 04:00:00 AM',
                'mm/dd/yyyy hh:mi:ss am'
            )
            ses.status IS NOT NULL;

and i need to calculate the group sessions based on each student

ss.student_id = sf.student_id(For each student in group sessions i dont know whether its correct or not)

below logic what i written its giving error near that hours

CASE
    WHEN (ses.type = 0 and ses.session_type=1)--single session
    THEN  round(
           (nvl(
               ses.actual_endtime,
               ses.scheduled_endtime
           ) - nvl(
               ses.actual_starttime,
               ses.scheduled_starttime
           ) ) * 24 * 60,
           4
       ) 'hours'
           WHEN ((ses.type = 1 OR ses.type = 2) and ses.session_type=1 and ss.student_id = sf.student_id)--single session
    THEN GREATEST(round((ses.actual_endtime -  ses.actual_starttime)* 24 * 60,
           4),round((ses.scheduled_endtime -  ses.scheduled_starttime)* 24 * 60,
           4))'hours'
               WHEN (ses.type = 0  and ses.session_type=0)--group session
    THEN round(
           (nvl(
               ses.actual_endtime,
               ses.scheduled_endtime
           ) - nvl(
               ses.actual_starttime,
               ses.scheduled_starttime
           ) ) * 24 * 60,
           4
       )'hours'
          
           ELSE  
            
           WHEN ((ses.type = 1 OR ses.type = 2) and ses.session_type=0 and ss.student_id = sf.student_id)--group session
    THEN GREATEST(round((ses.actual_endtime -  ses.actual_starttime)* 24 * 60,
           4),round((ses.scheduled_endtime -  ses.scheduled_starttime)* 24 * 60,
           4)) END As hours

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 13, 2022 08:54 AM

Use below.

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,
    GETCS_TEACHERNAMESOFSTUDENT(ss.student_id) 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,
    (select value from fev_session_tag_detail where session_id=ses.session_id and lower(key)='curriculum') Curriculum,
    -- 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,
    CASE
    WHEN (ses.type = 0 and ses.session_type=1)--single session
    THEN  round(
        (nvl(
            ses.actual_endtime,
            ses.scheduled_endtime
        ) - nvl(
            ses.actual_starttime,
            ses.scheduled_starttime
        ) ) * 24 * 60,
        4
    ) 
        WHEN ((ses.type = 1 OR ses.type = 2) and ses.session_type=1 and ss.student_id = sf.student_id)--single session
    THEN GREATEST(round((ses.actual_endtime -  ses.actual_starttime)* 24 * 60,
        4),round((ses.scheduled_endtime -  ses.scheduled_starttime)* 24 * 60,
        4))
            WHEN (ses.type = 0  and ses.session_type=0)--group session
    THEN round(
        (nvl(
            ses.actual_endtime,
            ses.scheduled_endtime
        ) - nvl(
            ses.actual_starttime,
            ses.scheduled_starttime
        ) ) * 24 * 60,
        4
    )
                   
        ELSE  
                     
        WHEN ((ses.type = 1 OR ses.type = 2) and ses.session_type=0 and ss.student_id = sf.student_id)--group session
    THEN GREATEST(round((ses.actual_endtime -  ses.actual_starttime)* 24 * 60,
        4),round((ses.scheduled_endtime -  ses.scheduled_starttime)* 24 * 60,
        4)) END As hours
                
                
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  and lower(fevsesstag.key)= 'curriculum')
    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(
            '04/09/2022 04:00:00 AM',
            'mm/dd/yyyy hh:mi:ss am'
        )
    AND
        ses.scheduled_endtime < TO_DATE(
            '05/09/2022 04:00:00 AM',
            '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;