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 #