Oracle Date and Time Question

[jayoung]

I am wanting to calculate the time between The FirstScan and The LastScan
then divide the time by the total number of traps

And this is the query

select TRAPPER, COUNT(BARCODE) as NOBARCODES, (MIN(to_char(t_date, 'MM-DD-YY HH24:mi:ss'))) as FIRSTSCAN,
(MAX(to_char(t_date, 'MM-DD-YY HH24:mi:ss'))) as LASTSCAN
From forweevil2003.Trapping
WHERE TRAPPER = '429948233'
and t_date between to_date('05/22/03 00:00:00','mm/dd/rr hh24:mi:ss')
and to_date('05/22/03 23:59:59','mm/dd/rr hh24:mi:ss')
GROUP BY TRAPPER
/
TRAPPER NOBARCODES FIRSTSCAN LASTSCAN
--------------- ---------- ----------------- -----------------
429948233 76 05-22-03 08:30:34 05-22-03 13:54:12

[jkoopmann]

if you want the time between two date fields, just subtract the last scan from the first scan. the difference will be in days so if you want to get down to the seconds multiply by the number of seconds in a day (86400).

don't know what "traps" is but if it is the count column you have just have put into the select :

(MAX(t_date)-MIN(t_date)) / count(barcode)