Clarence,
What is the definition of duration? Is it (CheckOut - CheckIn), or
(CheckIn - CheckOut), or (DATETIME(.#DATE, .#TIME) - CheckOut)?
I'm guessing it is (CheckIn - CheckOut) ("How long were items
checked out that are now checked back in?") I can't tell from your
description if these are like books being checked out and then later
being checked in, or like people checking in, and then later checking
out, and what exactly we are reporting on. So If I'm guessing wrong,
you'll have to change of lot of the details below based on my erroneous
assumptions. But the basics show you how to get a lapsed time, and
translate it into days, hours, and minutes.
What is your TIME FORMAT setting? If it has SS at the end, then
your integer units when you subtract one time from another are
seconds. It if has SS.SSS at the end, then your units are
milliseconds. If you need milliseconds, then add a "* 1000" in the
multiplcations below that end with "60 * 60".
You can either create a view, or add computed columns to your table,
that calculate lapsed time. I'm going to assume you use a time format
of HH:MM:SS. Or use a combination of the two, which I will do here,
adding the computed column LapsedSeconds.
ALTER TABLE ResUsage ADD COLUMN LapsedSeconds +
= (CheckIn - CheckOut) INTEGER
COMMENT ON LapsedSeconds IN ResUsage IS +
'Assumes HH:MM:SS date format'
CREATE VIEW LapsedTime +
(ResType, ResID, CheckOut, CheckIn, EmpID, LapsedSeconds, +
LapsedDays, LapsedHours, LapsedMin) +
AS +
SELECT +
ResType, ResID, CheckOut, CheckIn, EmpID, LapsedSeconds, +
(INT (LapsedSeconds / (24 * 60 * 60) )), +
(MOD ( INT (LapsedSeconds / (24 * 60 * 60))), 24 )), +
(MOD ( INT (LapsedSeconds / (24 * 60 * 60))), (24 * 60) ))
FROM ResUsage
SELECT COUNT (*), +
LapsedDays, +
LapsedMinutes +
FROM LapsedTime +
WHERE (whatever) +
GROUP BY LapsedDays, LapsedMinutes
On Tue, 28 Aug 2001 12:01:10 -0600, Clarence W. Robison wrote:
> create table ResUsage( +
> ResType Text 16, +
> ResID Text 8, +
> CheckOut DateTime, +
> CheckIn DateTime, +
> EmpID Integer )
