>> I am writing a helpdesk system which records agents logging in and
out of the system. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Does your boss, who is paying you, hide this
information and expect you to get your job done?

You might also want to learn that the only format for temporal data in
Standard SQL is ISO-8601 (yyyy-mm-dd hh:mm:ss.sss...) and start using
it; you can never tell, other systems just might follow iSO standards


>> I need to write a stored procedure which will show the peak number
of agents logged in concurrently during a specified time period. <<

Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?

CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));

>> The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<

Let's fill up a table of ranges:

CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));

In standard SQL, we have a predicate for durations like this:

SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);

That predicate gets translated into this:

(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))

Yes, it is a bit weird because it has to handle NULLs in the general
case.

You might also want to look up Rick Snodgrass at the University of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.