Subject Re: [ib-support] Help - Sum(TimeType)
Author Raul Chirea
Hi,

Wouldn't be nice to do like this:

create table EmployeeTimeWorked (
...
EmployeeID char(3) not null,
DateWorked date,
start timestamp not null,
stop timestamp,
HoursWorked numeric(15,2)
...
);

create trigger EmployeeTimeWorked_BI for EmployeeTimeWorked
before insert as
begin
new.DateWorked = cast(new.start as date);
new.HoursWorked = null;
if (new.start is not null) and (new.stop is not null) then
new.HoursWorked = select cast(stop - start) * 24 as numeric(15,2));
end

create trigger EmployeeTimeWorked_BI for EmployeeTimeWorked
before update as
begin
new.DateWorked = cast(new.start as date);
new.HoursWorked = null;
if (new.start is not null) and (new.stop is not null) then
new.HoursWorked = select cast(stop - start) * 24 as numeric(15,2));
end

And after that you can do any statistics you need. For example, what you
wanted was:

select sum(HoursWorked) from EmployeeTimeWorked
where EmployeeID = :ID
and DateWorked >= :date_first
and DateWorked <= :date_last

Raul.