I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.
Unfortunately, this is just a clunky thing to do in SQL. You can try this and see if it is any faster. One lest nested subquery....
from Daily_Trolley Current
inner join --PriorReadings
Max(DTB.Date) as PreviousDate
from Daily_Trolley DTA
left outer join Daily_Trolley DTB
on DTA.Trolley_num = DTB.Trolley_num
and DTA.Date > DTB.Date
group by DTA.Trolley_num,
on Current.Trolley_num = PriorReadings.Trolley_num
and Current.Date = PriorReadings.Date
left outer join Daily_Trolley Previous
on PriorReadings.Trolley_num = Previous.Trolley_num
and PriorReadings.PreviousDate = Previous.Date
If it's not practically useful, then it's practically useless.
Forgive me if this is a duplicate; I got an error posting a reply and it's not showing up. This is the third try.
Thanks blindman. QA didn't like "Current" as an alias, but it worked fine when I changed that. Both our versions return records so quickly that no time is registered in the execution time window in QA. There are only 10k records in this table though. However, your version lets me calculate the difference; mine wouldn't (not directly anyway).
I may modify both to work against another table with several hundred thousand records, and see how they compare. I suspect yours will be faster due to the join instead of subquery.