Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

    Unanswered: Previous value best method

    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:

    Code:
    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
    WHERE A.Date='1/23/2005'
    ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.
    Paul

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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....

    Code:
    select	Current.Trolley_num,
    	Current.Date,
    	Current.Speedo_start,
    	Current.Speedo_end,
    	Previous.Speedo_end
    from	Daily_Trolley Current
    	inner join --PriorReadings
    		(select	DTA.Trolley_num,
    			DTA.Date,
    			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,
    			DTA.Date) PriorReadings
    		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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.

    Thanks again.
    Paul

Posting Permissions

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