Re: [Q] date-time substraction for hours
You need a self-join.
Here is an SQL I think answers your problem.
-----------------------------------------------------------
select a.lname, a.fname, a.vendor, a.date_time_in,
min(b.date_time_in) out, min(b.date_time_in) - a.date_time_in span
from Hist_visitors a , Hist_visitors b
where a.lname=b.lname
and a.fname=b.fname
and a.vendor=b.vendor
and a.status = 'IN'
and b.status = 'OUT'
and b.date_time_in > a.date_time_in
group by 1,2,3,4
into temp t1 with no log;select date( extend( date_time_in, year to day)), lname, fname, vendor,
sum(span)
from t1
group by 1,2,3,4
order by 2,3,4,1
-----------------------------------------------------------
Scott Rubin
At 04:23 PM 4/4/96 -0600, you wrote:
>I need to know if there is a way to do the following in SQL.
>
>I have a historical database for visitors. Every time an entry is made to
the system,
>the following fields are inserted into my Hist_visitors table,
>lname, fname, vendor, date_time_in, status
>Lennon, John, Beatles, 1996-03-01 10:41 PM, IN
>and on exiting the site
>Lennon, John, Beatles, 1996-03-02 2:30 AM, OUT
>
>My question is for finding out the number of man hours spend by visitors
inside our
>site - for every day, allowing for repeat visitors. I have a couple of
ideas but
>nothing concrete. If someone has done anything like this before I would
love to hear
>their suggestions.
>
>
>
>--
>Saqib Mausoof
>--------------------------------------------------------------------
>I have a hard enough time speaking for myself, so speaking on behalf
>of my employer is out of the question.
>
>