SQL & PL/SQL :: Procedure To Calculate Summary Session?

I have a below requirement from client.

Create a procedure generate_daily_session_summary that will read the pcm_session_summary (pk sess_id) table and create a per day summary in the pcm_session_summary_daily table.

1. Summarize all records from pcm_session_summary for session start timestamp (START_TS) greater than the max existing SESS_DT from pcm_session_summary_daily table and less than the start of current day. Insert the records into the pcm_session_summary_daily table.

2. Populate the fields in pcm_session_summary_daily as follows :

sess_dt - truncated date from pcm_session_summary.start_ts unique_user_cnt - count of unique cws ids encountered in that day, sess_cnt number - count of total sessions in that day , max_concurrent_sess_cnt number - maximum number of concurrent sessions at any point of time in that day. Concurrent sessions are defined as those that have overlapping start_ts or end_ts - sessions that were active at the same moment in time would be concurrent with each other.

3. The procedure will be scheduled to run every day in a batch job and will summarize session data until the end of the previous day

I have the table with below details.

desc PCM_SESSION_SUMMARY
Name Null Type
---------- -------- ------------
SESS_ID NOT NULL NUMBER
USER_CUPID VARCHAR2(10)
START_TS TIMESTAMP(6)
END_TS TIMESTAMP(6)
ACT_CNT NUMBER

[code]....

I have written the below query to get the result for question no:1

select trunc(start_ts,'DDD') start_ts,count(distinct(user_cupid)) user_cnt,count(SESS_ID) Sess_cnt
from PCM_SESSION_SUMMARY
where START_TS < sysdate or START_TS > (select max(sess_dt) from pcm_session_summary_daily where sess_dt < sysdate)
group by trunc(start_ts,'DDD')
order by start_ts;

However i tried a lot but not sure how to get maximum concurrent session count for a day.One of my friend comment below.

The Pcm_session_summary_daily is empty so you need to handle nulls when doing max. That will fix the issue of no results for the Greater Than condition.

You need to get concurrent count of sessions with respect to one another, the query that you have will not work. Do it in PL/SQL using procedural logic. The queries would return multiple rows, you need to handle that.