Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    59

    Arrow 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. #2
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    59

    Works!!!

    Perfectly works...

    Thanks a lot!!!

  4. #4
    Join Date
    Nov 2002
    Posts
    59

    Curiosity

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

    Thanks

    Quote 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. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cuneyt
    Just a curiosity!!! Why did you Group By twice as A.TheDate ?
    Typo
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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