A classroom records its student movement IN and Out of the classroom in a table as shown below:

 ------- --------- --------------------- 
| stuId | actions | recorded            |
 ------- --------- --------------------- 
| 1     | IN      | 2021-05-01 10:00:00 |
 ------- --------- --------------------- 
| 1     | OUT     | 2021-05-01 14:00:00 |
 ------- --------- --------------------- 
| 1     | IN      | 2021-05-01 16:00:00 |
 ------- --------- --------------------- 
| 1     | OUT     | 2021-05-01 20:00:00 |
 ------- --------- --------------------- 
| 2     | IN      | 2021-05-01 09:00:00 |
 ------- --------- --------------------- 
| 2     | OUT     | 2021-05-01 13:00:00 |
 ------- --------- --------------------- 
| 2     | IN      | 2021-05-01 15:00:00 |
 ------- --------- --------------------- 

We have to calculate the amount of hour spent by each student inside the classroom.

Also, for student 2 the last action is 'IN'. So, that student is still studying inside the class. So we have to add this hour also while the student is still studying. We can use current time to calculate it.

What is the proper way to solve this problem statement?

CodePudding user response：

select 
    main.stuid, 
    main.in_date, 
    case when main.out_date is null then localtimestamp(0) else main.out_date end as out_date, 
    case when main.out_date is null then localtimestamp(0)::timestamp - main.in_date else main.out_date - main.in_date end as stay_in_class,
    case when main.out_date is null then 'in class now' else 'came out' end as stu_status 
from 
    (
        select 
            t_in.stuid, 
            t_in.recorded as in_date, 
            min(t_out.out_date) as out_date 
        from 
            test.in_class t_in 
        left join 
            (
                select stuid, recorded as out_date
                from test.in_class  
                where actions = 'OUT'
            ) t_out on t_in.stuid = t_out.stuid and t_in.recorded < t_out.out_date
        where 
            t_in.actions = 'IN'
        group by t_in.stuid, t_in.recorded
        order by t_in.stuid, in_date, out_date
    ) main

Result of this query:

CodePudding user response：

You need to calculate the difference between the consecutive IN's and OUT's. This can be done by joining the table with itself and get the rows where row number difference is 1.

Please try the postgres sql query below:

WITH student_cte AS (
    SELECT 
        stuId, 
        actions,
        recorded,
        row_number() over (partition by stuId order by recorded) as rn
    FROM
        student
)
select c1.stuId as StuID, SUM((COALESCE(c2.recorded, now()::timestamp) - c1.recorded)) as total_in_duration
FROM
student_cte c1
  LEFT JOIN 
student_cte c2 
on c1.stuId = c2.stuId and c1.rn   1 = c2.rn
where c1.actions='IN'
GROUP BY c1.stuId;