Maybe this goes faster ?

CREATE PROCEDURE MonthTable ( TheYear SMALLINT, Dept_No SMALLINT )
RETURNS ( Emp_No INTEGER, Name VARCHAR(50), M01 DECIMAL (9,2),
M02 DECIMAL (9,2), M03 DECIMAL (9,2),
M04 DECIMAL (9,2), M05 DECIMAL (9,2),
M06 DECIMAL (9,2), M07 DECIMAL (9,2),
M08 DECIMAL (9,2), M09 DECIMAL (9,2),
M10 DECIMAL (9,2), M11 DECIMAL (9,2),
M12 DECIMAL (9,2) )
AS
declare variable myEmp_No integer;
BEGIN
for select distinct Emp_No from Pers
where
order by Emp_no
into :myEmp_No
do
begin
mo1 = 0;
mo2 = 0;
....
mo12 = 0;

FOR SELECT Emp_No, NAME1 || ' ' || NAME2, Work_Hours
FROM Pers
WHERE Dept_No = :Dept_No

order by Emp_No,Work_Day
where and Emp_No = myEmp_No
and extract(year of Work_Day) = :TheYear // prob want year limit

INTO :Emp_No, :Name DO

BEGIN
myMonth = extract(month of Work_Day);
if (MyMonth = 1) then
Mo1 = Mo1 + Work_Hours;
else
Mo2 = Mo2 + Work_Hours;
else
....

else
Mo12 = ....

end
suspend;
end
end

Hans
-------------------------------------------------------------------

Richard Saeger wrote:

>
> Hi,
>
> I have a table with worktime per employee and day:
> Emp_No INTEGER
> Work_Day DATE
> Work_Hours DEZIMAL (9,2)
>
> Now I need a report with Work_Hours per employee and month:
> Emp_No Name Jan Feb ... Dec
>
> (say: a horizontal table)
>
> I do it with the procedure MonthTable below, but there are 12
> SELECT's per employee, it takes a long time to work.
> Is there any faster solution to do it?
>
> Thanks in advance
> Richard
>
> CREATE PROCEDURE MonthTable ( TheYear SMALLINT, Dept_No SMALLINT )
> RETURNS ( Emp_No INTEGER, Name VARCHAR(50), M01 DECIMAL (9,2),
> M02 DECIMAL (9,2), M03 DECIMAL (9,2),
> M04 DECIMAL (9,2), M05 DECIMAL (9,2),
> M06 DECIMAL (9,2), M07 DECIMAL (9,2),
> M08 DECIMAL (9,2), M09 DECIMAL (9,2),
> M10 DECIMAL (9,2), M11 DECIMAL (9,2),
> M12 DECIMAL (9,2) )
> AS
> DECLARE VARIABLE StartDate DATE;
> DECLARE VARIABLE EndDate DATE;
> BEGIN
> FOR SELECT Emp_No, NAME1 || ' ' || NAME2, Work_Hours
> FROM Pers
> WHERE Dept_No = :Dept_No
> INTO :Emp_No, :Name DO
> BEGIN
> EXECUTE PROCEDURE EncodeDate(TheYear, 1, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 1) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M01;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 2, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 2) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M02;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 3, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 3) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M03;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 4, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 4) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M04;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 5, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 5) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M05;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 6, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 6) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M06;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 7, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 7) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M07;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 8, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 8) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M08;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 9, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 9) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M09;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 10, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 10) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M10;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 11, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 11) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M11;
>
> EXECUTE PROCEDURE EncodeDate(TheYear, 12, 1) RETURNING_VALUES
> (StartDate);
> EXECUTE PROCEDURE EOM(TheYear, 12) RETURNING_VALUES(EndDate);
> SELECT SUM(Work_Hours) FROM WorkTime WHERE Emp_No = :Emp_No AND
> (Work_Day BETWEEN :StartDate AND :EndDate) INTO M12;
>
> SUSPEND;
> END
> END #
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/