Subject Re: [ib-support] Help - Sum(TimeType)
Author Helen Borrie
At 08:22 PM 25-07-02 -0300, you wrote:
>The Employee work every day in different times, and he can start and stop to
>job any time. for example:
>
>In the date 01-Jul-2002 he started at 08:00 am and stop at 11:30 am, so he
>worked 03:30.
>In the date 02-Jul-2002 he started at 08:30 am and stop at 11:45 am, so he
>worked 03:15.
>In the date 03-Jul-2002 he started at 08:15 am and stop at 12:00 pm, so he
>worked 03:45.
>
>I want to pay for him job
>
> Total hours worked: 10:30
> Price by hour: $ 50,00
> Amount: $ 525,00

As Fred explained, the TIME data type does not record an interval of time,
it records the time of day. It is just like a TIMESTAMP with no DATE
portion recorded.

Here is your table declaration:


> EmplyeeID Char(3)
> DateWorked Date
> HoursWorked Time

and here is the statement you used:

>
> SELECT SUM(HourWorked) FROM EmployeeTimeWork
> WHERE EmployeeID = :ID
> AND DateWorked >= :DateFirst
> AND DateWorked <= :DateLast

Your mistake is that you have used the wrong data type for HoursWorked. It
needs to be either integer (if you are recording in minutes) or
decimal(5,2) (if you are recording in hours and part-hours). If the
latter, your application will need to convert part-hours to a decimal
(minutes/60).

Take care with the precision of your calculations on part-hours if you are
using dialect 3, where the result of integer/integer gets truncated to the
next-lowest integer.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________