Time elapsed between Dates (1 Viewer)

Hello,

I need to calculate the time (in years) that has elapsed between date values in 3 separate records. Here is a look at the data.

PID Date
73403 15-Nov-68
73403 11-Aug-95
73403 01-Jan-08

PID is the unique ID of the person, and you will see it is the same over these 3 records. I need to know how many years have elapsed, starting from the oldest date (15-Nov-68 to 11-Aug-95 ...and...11-Aug-95 to 01-Jan-08). Ideally I would like to have a result like this:

PID Date Inverval 1 Date Interval 2
73403 27 yrs 13yrs

Any help would be appreciated. Thank you.

something like

SELECT PID,Datediff("yyyy",(SELECT Max(Date) FROM myTable T WHERE PID=myTable.PID AND Date<myTable.Date),myTable.Date) & "yrs" AS Interval
FROM myTable
ORDER BY PID, Date

Click to expand...

Note Date is a reserved word and should not be used - change to something else

Not sure if you can get this into one row - you would use a crosstab to do that, but I seem to recall crosstabs and subqueries don't mix