Require assistance in Constructing a View

  • Hi guys

    i've a table "FuelLog" with fields "FuelLogID, VehicleID, Date, FuelQty, EndingOdometer"

    i'm having trouble constructing a View to show the 'change' in odometer readings (difference in the current reading & previous reading), e.g.

    FuelLogID VehicleID Date FuelQty EndOdometer Change

    ======= ======= ==== ====== ========== ======

    1 101 08/15/08 45 12100 ?

    4 101 08/21/08 52 12900 ?

    5 101 08/26/08 48 13500 ?

    thanks in advance.

    Jerry

  • [font="Courier New"]-- make some sample data

    SET DATEFORMAT MDY

    DROP TABLE #Temp

    CREATE TABLE #Temp (FuelLogID INT, VehicleID INT, [Date] DATETIME, FuelQty INT, EndOdometer INT)

    INSERT INTO #Temp (FuelLogID, VehicleID, [Date], FuelQty, EndOdometer)

    SELECT 1, 101, '08/15/08', 45, 12100 UNION ALL

    SELECT 4, 101, '08/21/08', 52, 12900 UNION ALL

    SELECT 5, 101, '08/26/08', 48, 13500

    -- example 1

    SELECT FuelLogID, VehicleID, [Date], FuelQty, EndOdometer,

       Change = EndOdometer - (SELECT MAX(EndOdometer) FROM #Temp WHERE VehicleID = t.VehicleID AND FuelLogID < t.FuelLogID)

    FROM #Temp t

    -- example 2

    SELECT a.FuelLogID, a.VehicleID, a.[Date], a.FuelQty, a.EndOdometer,

       Change = a.EndOdometer - b.EndOdometer

    FROM #Temp a

    LEFT JOIN #Temp b ON b.VehicleID = a.VehicleID

       AND b.EndOdometer = (SELECT MAX(EndOdometer) FROM #Temp WHERE VehicleID = a.VehicleID AND FuelLogID < a.FuelLogID)

    [/font]

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • How about something like this:

    create view FuelLog

    as

    with CTE (Row, FuelLogID, VehicleID, Date, FuelQty, EndingOdometer) as

    (select row_number() over (partition by VehicleID order by Date),

    FuelLogID, VehicleID, Date, FuelQty, EndingOdometer

    from dbo.FuelLog)

    select c1.FuelLogID, c1.VehicleID, c1.Date, c1.FuelQty, c1.EndingOdometer,

    isnull(c1.EndingOdometer-c2.EndingOdometer, c1.EndingOdometer) as Change

    from CTE c1

    left outer join CTE c2

    on c1.VehicleID = c2.VehicleID

    and c1.Row = c2.Row+1

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The main issue here is to get the previous odometer readings, given the current vehicle ID and reading date. So, first I would setup a function that would return the previous odometer readings where the input is the current vehicle ID and the date.

    Now with both the current, and the previous data, you can easily build the view.

    The more you are prepared, the less you need it.

  • Andrew Peterson (8/27/2008)


    The main issue here is to get the previous odometer readings, given the current vehicle ID and reading date. So, first I would setup a function that would return the previous odometer readings where the input is the current vehicle ID and the date.

    Now with both the current, and the previous data, you can easily build the view.

    Andrew, this sounds very promising - care to show how it's done, using the test data already posted?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Why build a function for it? That's row-by-row processing. The join I gave you should do what you need. I've used that type of construct many times and it's usually quite fast and efficient.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Will do. A bit busy today, but will see if I can work it out.

    If not, will work over tonight.

    The more you are prepared, the less you need it.

  • GSquared (8/27/2008)


    Why build a function for it? That's row-by-row processing. The join I gave you should do what you need. I've used that type of construct many times and it's usually quite fast and efficient.

    It's always fast and efficient. We need Andrew's code to prove just how efficient with a large sample table.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I have to agree, joins should be faster than functions - which you expect to be RBAR.

    driven by curiosity, I've done quick process to build a large table with lots of vehicles, each with many fuel records. I've used RAND quite liberally to keep from building any inadvertant consistency.

    But I'm getting the CTE to work just yet. taking alook now

    The more you are prepared, the less you need it.

  • And the winner is...

    the CTE based view, by a landslide.

    Actually, it was not even close, which makes perfect sense.

    I populated a table holding just under 100,000 records, then compared the two views, one as listed above, and the other using a function.

    The more you are prepared, the less you need it.

  • hi guys

    many thanks to all that responded to my request for help. i rec'd not only a solution but also alternative methods of a solution.

    i'm gonna throw in another here, whats the performance of a trigger based solution to my original request. for example;

    what if i use a trigger to update a 'startodometer' field in the table i.e (after insert, find 'previous ending odometer' update the inserted record).

    sample code:

    Select @FuelLogID = (Select FuelLogID From Inserted)

    Select @EquipID = (Select EquipID From Inserted)

    Set @prevOdometer = (Select Max(EndingOdometer) From FuelLog Where FuelLogID < @FuelLogID AND EquipID = @EquipID)

    Update FuelLog

    Set StartingOdometer = @prevOdometer

    Where FuelLogID = @FuelLogID

  • Trigger could work, but make sure that it will work for multiple rows (the variables in your sample code will break it if multiple rows are added at the same time), and make sure it can account for corrections, etc., in the data.

    Making a trigger account for all the possible ways that the data can be entered, updated, fixed, etc., is usually harder than just querying the data.

    As always, if you're certain that a trigger will handle what you need, go for it. Just be very, very certain before you implement, because you don't want to have to fix data afterwards if the trigger turns out to cause a problem.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply