Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    3

    Unanswered: Comparing Row Values

    I have what I hope to be a simple question. I want to compare the row vaues of the same field.
    Example:
    Employee Purchace Dates:
    1/2/05
    3/3/05
    6/6/05
    7/1/05

    I need to know how I can compare these values to one another (DateDiff, DateAdd, etc).


    Thanks

    Dan

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Dan - welcome to the forum

    SQL typically compares values within rows rather than values between rows like this. Perhaps you could be more specific about what precisely you want to achieve, ideally including examples of data in the table and the output you hope to achieve
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2005
    Posts
    3

    Exactly!

    I'm fine as long as I'm comparing values in the same row, but what I want to do is compare multiple start dates for an employee and find the difference between them. Its one of those things that is easy in Excel and quite do-able in ASP (Web).
    The whole objective is to find the number of days elapsed from one start date to the next using linked tables and bound forms/reports.

    Thanks for the warm reception!

    Example

    People:
    EmpID
    EmpName

    StartDates:
    EmpID
    StartDate

    One Employee has multiple start dates.
    Last edited by danbaker30; 10-27-05 at 12:08.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Dan

    You can get the difference between the earliest and latest start dates e.g.:

    Code:
     
    SELECT StartDates.EmpID, EmpName, DATEDIFF("d", Min(StartDate), Max(StartDate)) AS NoDaysDiff
    FROM StartDates INNER JOIN People ON StartDates.EmpID = People.EmpID 
    Group By StartDates.EmpID, EmpName
    Of use?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Posts
    3

    Its close

    Its pretty much the way I'm handling it now, but was hoping for something a bit more granular (Like compaing the differences of 5 or more dates for a given employee).
    I'm using some VBA tricks with the detail and grouping levels to accomplish this but its very labor intensive and I was hoping that someone had come up with a more streamlined approach.
    I do thank you so much for your attention!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The problem you have is that there are n start dates so you can't write a one size fits all query. You could play around with SQL for specific questions e.g. below shows the difference between the second earliest and second latest start dates:

    Code:
     SELECT StartDates.EmpID, EmpName, DATEDIFF("d", Min(StartDate), Max(StartDate)) AS NoDaysDiff
    FROM StartDates INNER JOIN People ON StartDates.EmpID = People.EmpID 
    WHERE StartDate NOT IN(SELECT MAX(StartDate) FROM StartDates B WHERE B.EmpID = StartDates.EmpID UNION SELECT MIN(StartDate) FROM StartDates B WHERE B.EmpID = StartDates.EmpID)
    Group By StartDates.EmpID, EmpName
    I'm not sure how but maybe a crosstab could.... nah, probably not.

    HTH
    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
  •