Calculate difference between start time and end time in Oracle Database

zeeshanpas
 
on May 09, 2022 05:27 AM
408 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'
                    )
--                AND
--                    nvl(
--                        sc.is_test_data,
--                        0
--                    ) != 1
--                AND
--                    ses.school_id NOT IN (
--                        213,358
--                    )
                AND
                    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
zeeshanpas
 
on May 13, 2022 07:46 AM

https://ibb.co/47yb0gR

I shared the error details in this screen shot

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;

 

arjunv
 
on May 13, 2022 11:55 PM
zeeshanpas says:
geeting another error if i execute this error details in below screenshot

Please refer below link.

https://www.yawintutor.com/ora-00907-missing-right-parenthesis/