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
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.
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!
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:
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.