Subject | Re: [ib-support] Help - Sum(TimeType) |
---|---|
Author | Raul Chirea |
Post date | 2002-07-26T09:45:02Z |
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.
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.