Select interval between timestamped records

[ericbrunson]

I have the following schema:

Person
------
Person_id (pk)

Visit
------
Visit_id (pk)
Person_id (fk)
Visit_date

I need to select the date of each Person's last visit and at the same time calculate the amount of time since their last visit, if there was one.

Anyone got a clever query for that?

 

[SantaMufasa]

Eric,

Here is some code for you:

Sample data:

select * from person;

 PERSON_ID PERSON_NAME
---------- ------------
        22 Eric Brunson
        26 Dave Hunt
        28 Dave Bridges

select visit_id,person_id
      ,to_char(visit_date,'dd-MON-yy hh24:mi:ss') Visit_date
from visit;

  VISIT_ID  PERSON_ID VISIT_DATE
---------- ---------- ------------------
         7         28 10-AUG-05 14:42:41
         7         28 11-AUG-05 13:13:05
         9         22 03-AUG-05 09:58:50

Selection code:

col a heading "Most|Recent|Visit" format a21
col b heading "Elapsed|Time|Since|Last|Visit|(Days)" format 9999.9
select p.person_id
      ,p.person_name
      ,nvl(to_char(v.visit_date,'dd-MON-yy hh24:mi:ss'),'No Visits') a
      ,sysdate-visit_date b
from visit v,person p
where v.person_id(+) = p.person_id
/

                                                 Elapsed
                                                    Time
                                                   Since
                           Most                     Last
                           Recent                  Visit
 PERSON_ID PERSON_NAME     Visit                  (Days)
---------- --------------- --------------------- -------
        22 Eric Brunson    03-AUG-05 09:58:50       19.3
        26 Dave Hunt       No Visits
        28 Dave Bridges    10-AUG-05 14:42:41       12.1
        28 Dave Bridges    11-AUG-05 13:13:05       11.1

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Eric,

Sorry...I reviewed my code and saw that I listed all visits for each person...you just want the latest visit. Here is the revised code to do that:
[/code]
col a heading "Most|Recent|Visit" format a21
col b heading "Elapsed|Time|Since|Last|Visit|(Days)" format 9999.9
select p.person_id
,p.person_name
,nvl(to_char(v.visit_date,'dd-MON-yy hh24:mi:ss'),'No Visits') a
,sysdate-visit_date b
from (select person_id, max(visit_date) visit_date from visit
group by person_id) v,person p
where v.person_id(+) = p.person_id
/

Elapsed
Time
Since
Most Last
Recent Visit
PERSON_ID PERSON_NAME Visit (Days)
---------- --------------- --------------------- -------
22 Eric Brunson 03-AUG-05 09:58:50 19.4
26 Dave Hunt No Visits
28 Dave Bridges 11-AUG-05 13:13:05 11.3
[/code]
Let me know if this is preferrable.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ericbrunson]

Very excellent, thanks a lot.

There may be more than two visits per person, but I think I can get the data I want by grouping by person id again and getting min( interval ). Unless you have a better idea.

 

[SantaMufasa]

Eric,

My followup (22 Aug @ 05 21:20) to my first post takes care of multiple visits per person...It take shows the interval of max(visit_date) [i.e. lastest visit] and SYSDATE.

Let us know if that is not what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ericbrunson]

Oh, I was scanning the thread at the house while trying to get ready for work, I guess I overlooked that...

I need the time between the last visit and the visit before. I thought you were joining the visit table against itself, but you definitely gave me a push the right direction, but the group bys are confusing me.

select 
  person.name, 
  last.visit_date, 
  min( last.visit_date - previous.visit_date )
from
  person,
  ( select person_id, max( visit_date )
    from visit
    group by person_id ) last,
  visit previous
where person.person_id = last.person_id
  and last.person_id = previous.person_id
  and last.visit_date > previous.visit_date
group by 
  person.name,
  last.visit_date;

Hmmm... That will actually calculate the intervals between the last visit and all other visits, then take the least one. It doesn't seem particularly efficient. Can you see an improvement?