How to find previous, current and future values

[DawnTreader]

Hello All

I have need to know in one "line" the previous value, current value and the
"future" value.

here is my scenario. i have a "service report", these can be entered at any
time and so sometimes people can be entering a service report for a past date
that "fits" between 2 other reports. the problem is that each time the same
product is serviced and a report is entered there are running hours.

this running hour value can only increase but if someone is putting in a
report that falls between 2 that are already entered then the running hours
must not be less than the previous report or greater than the "future" report
than the entered report is trying to fit into. this makes validating entries
something of a pain but it needs to be done.

i have a query that gives me the previous hours. is there a way to modify
this to also give me the next hours on the same line? if so i can get the
code to look up the 3 values in this query. here is the sql:

SELECT DISTINCTROW subtblRunningHours.RunningHoursID,
subtblRunningHours.RHDate, subtblRunningHours.HoursAtDate,
subtblRunningHours.ServiceReportID, subtblRunningHours.BlockID,
subtblRunningHours.IssueID, (SELECT TOP 1 DsubtblRunningHours.HoursAtDate
FROM subtblRunningHours AS DsubtblRunningHours WHERE
DsubtblRunningHours.BlockID = subtblRunningHours.BlockID AND
DsubtblRunningHours.RHDate < subtblRunningHours.RHDate ORDER BY
DsubtblRunningHours.RHDate DESC, DsubtblRunningHours.RunningHoursID) AS
PriorRH, (SELECT TOP 1 DsubtblRunningHours.RHDate FROM subtblRunningHours AS
DsubtblRunningHours WHERE DsubtblRunningHours.BlockID =
subtblRunningHours.BlockID AND DsubtblRunningHours.RHDate <
subtblRunningHours.RHDate ORDER BY DsubtblRunningHours.RHDate DESC,
DsubtblRunningHours.RunningHoursID) AS PriorRHDate
FROM subtblRunningHours
GROUP BY subtblRunningHours.RunningHoursID, subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate, subtblRunningHours.ServiceReportID,
subtblRunningHours.BlockID, subtblRunningHours.IssueID,
subtblRunningHours.HoursAtDate
ORDER BY subtblRunningHours.BlockID DESC , subtblRunningHours.HoursAtDate
DESC;

any and all help is appreciated.

 

[DawnTreader]

Hello All

i am sorry, i have been posting this for a couple of days now and it seemed
that i wasnt getting through. the other 2 posts have gone through and i
actually have answers on both. please disregard this third post.