# Thread: How to find the difference between to dates in different rows?

1. Registered User
Join Date
Nov 2002
Posts
59

## Unanswered: How to find the difference between to dates in different rows?

Hi everyone...

That's my report
Date..............Carriage No
12/06/2006..........2864
09/06/2006..........2862
26/06/2006..........2862
05/06/2006..........2862
08/06/2006..........2860
29/05/2006..........2860
01/06/2006..........2859
02/06/2006..........2857
28/06/2006..........2856
14/06/2006..........2856
13/06/2006..........2855
02/06/2006..........2814

So I would like to find date difference between same carriage numbers.
For Example 2862, how many days between first and second, second and third entry but I'd just skip if there is only one entry as 2859.

Thanks
Cuneyt
Last edited by cuneyt; 10-03-06 at 12:32.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi coneyt - hope this does it for you. If not - please could you post the results you do want.

Hey Teddy - I posted an answer to an identical question a couple of weeks ago. FAQ link?

Curiously I think I've come up with a totally different solution for this one....
Code:
```SELECT A.CarriageNo, A.TheDate, MIN(B.TheDate) AS BTheDate, DateDiff("d", A.TheDate, MIN(B.TheDate)) AS TheDiff
FROM CarriageTable AS A LEFT JOIN CarriageTable AS B ON A.CarriageNo = B.CarriageNo AND A.TheDate < B.TheDate
GROUP BY A.CarriageNo, A.TheDate,  A.TheDate
ORDER BY A.CarriageNo, A.TheDate```

3. Registered User
Join Date
Nov 2002
Posts
59

## Works!!!

Perfectly works...

Thanks a lot!!!

4. Registered User
Join Date
Nov 2002
Posts
59

## Curiosity

Just a curiosity!!! Why did you Group By twice as A.TheDate ?

Thanks

Originally Posted by pootle flump
Hi coneyt - hope this does it for you. If not - please could you post the results you do want.

Hey Teddy - I posted an answer to an identical question a couple of weeks ago. FAQ link?

Curiously I think I've come up with a totally different solution for this one....
Code:
```SELECT A.CarriageNo, A.TheDate, MIN(B.TheDate) AS BTheDate, DateDiff("d", A.TheDate, MIN(B.TheDate)) AS TheDiff
FROM CarriageTable AS A LEFT JOIN CarriageTable AS B ON A.CarriageNo = B.CarriageNo AND A.TheDate < B.TheDate
GROUP BY A.CarriageNo, A.TheDate,  A.TheDate
ORDER BY A.CarriageNo, A.TheDate```

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by cuneyt
Just a curiosity!!! Why did you Group By twice as A.TheDate ?
Typo

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•