At 12:47 AM 22/05/2004 +0000, you wrote:

>Hi,
>
>Somebody have to help me with my Stored Procedure.
>
>The purpose of the stored procedure is to get the time worked.
>
>That is when login time and logout time is given, I need to get the
>time logged in to the system.
>
>For example if two dates May 21 and May 28 given for a particular
>user then I have to calculate the hours worked from the systemlog
>table which has ID, USERID, LOGIN, LOGOUT,REGISTERID
>
>MY SP
>
>CREATE PROCEDURE GETHOURSWORKED(
>USERID INTEGER,
>FROMTIME TIMESTAMP,
>TOTIME TIMESTAMP)
>
>RETURNS (HOURSWORKED NUMERIC(2,2))
>
>AS
>
>BEGIN
>
> FOR SELECT SYSTEMLOG.LOGOUT - SYSTEMLOG.LOGIN FROM SYSTEMLOG
> WHERE SYSTEMLOG.USERID =:USERID AND
> SYSTEMLOG.LOGIN =:FROMTIME AND SYSTEMLOG.LOGOUT = :TOTIME INTO
>HOURSWORKED
> DO SUSPEND;
> END

If you want a single total, something like this should do it:

CREATE PROCEDURE GETHOURSWORKED(
USERID INTEGER,
FROMTIME TIMESTAMP,
TOTIME TIMESTAMP)

RETURNS (HOURSWORKED NUMERIC(2,2))

AS
DECLARE LOGIN TIMESTAMP;
DECLARE LOGOUT TIMESTAMP;
DECLARE SESSIONHOURS NUMERIC(2,2);

BEGIN
SESSIONHOURS = 0.00;
HOURSWORKED = 0.00;
FOR SELECT LOGIN, LOGOUT FROM SYSTEMLOG
WHERE USERID =:USERID
AND LOGIN >= :FROMTIME
AND LOGOUT <= :TOTIME
INTO :LOGIN, :LOGOUT
DO
BEGIN
SESSIONHOURS = CAST((LOGOUT - LOGIN)/24 AS NUMERIC(2,2);
HOURSWORKED = HOURSWORKED + SESSIONHOURS;
SESSIONHOURS = 0.00;
END
SUSPEND;
END

If you want a multi-row result, i.e. one result for each session:

CREATE PROCEDURE GETHOURSWORKED(
USERID INTEGER,
FROMTIME TIMESTAMP,
TOTIME TIMESTAMP)

RETURNS (HOURSWORKED NUMERIC(2,2))

AS
DECLARE LOGIN TIMESTAMP;
DECLARE LOGOUT TIMESTAMP;

BEGIN
HOURSWORKED = 0.00;
FOR SELECT LOGIN, LOGOUT FROM SYSTEMLOG
WHERE USERID =:USERID
AND LOGIN >= :FROMTIME
AND LOGOUT <= :TOTIME
INTO :LOGIN, :LOGOUT
DO
BEGIN
HOURSWORKED = CAST((LOGOUT - LOGIN)/24 AS NUMERIC(2,2);
SUSPEND;
HOURSWORKED = 0.00;
END
END

Note that, by this logic, you will NEVER collect data on sessions that
began before FROMTIME or finished after TOTIME. To ensure that you
captured everything in successive calls, you would have to include OR logic
to include also the time logged in during the boundaries specified by the
input parameters, for those sessions. For example, for the second version
of the procedure:

CREATE PROCEDURE GETHOURSWORKED(
USERID INTEGER,
FROMTIME TIMESTAMP,
TOTIME TIMESTAMP)

RETURNS (HOURSWORKED NUMERIC(2,2))

AS
DECLARE LOGIN TIMESTAMP;
DECLARE LOGOUT TIMESTAMP;

BEGIN
HOURSWORKED = 0.00;
FOR
SELECT LOGIN, LOGOUT FROM SYSTEMLOG
WHERE USERID =:USERID
AND (
(LOGIN >= :FROMTIME AND LOGOUT <= :TOTIME )
OR
(LOGIN BETWEEN :FROMTIME AND :TOTIME)
OR
(LOGOUT BETWEEN :FROMTIME AND :TOTIME)
)
INTO :LOGIN, :LOGOUT
DO
BEGIN
IF (LOGOUT > :TOTIME) THEN
LOGOUT = :TOTIME;
IF (LOGIN < :FROMTIME) THEN
LOGIN = :FROMTIME;
HOURSWORKED = CAST((LOGOUT - LOGIN)/24 AS NUMERIC(2,2);
SUSPEND;
HOURSWORKED = 0.00;
END
END

/heLen