--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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

Dear Helen

Thanks for your help.

I got the SP working with little changes.

Instead of dividing by 24 , we have to multiply by 24
and change numeric to integer, I have given working SP below

CREATE PROCEDURE GETHOURSWORKED(
USERID INTEGER,
FROMTIME TIMESTAMP,
TOTIME TIMESTAMP)
RETURNS (
HOURSWORKED INTEGER )
AS
DECLARE LOGIN TIMESTAMP;
DECLARE LOGOUT TIMESTAMP;
DECLARE SESSIONHOURS INTEGER ;

BEGIN
SESSIONHOURS = 0;
HOURSWORKED = 0;
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 INTEGER);
HOURSWORKED = HOURSWORKED + SESSIONHOURS;
SESSIONHOURS = 0;
END
SUSPEND;
END

Regards,

Muthu Annamalai