August 26, 2008 at 11:05 pm
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
August 27, 2008 at 3:37 am
[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]
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
August 27, 2008 at 7:10 am
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
August 27, 2008 at 7:22 am
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.
August 27, 2008 at 7:29 am
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?
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
August 27, 2008 at 7:54 am
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
August 27, 2008 at 8:03 am
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.
August 27, 2008 at 8:14 am
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.
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
August 27, 2008 at 9:13 am
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.
August 27, 2008 at 10:29 am
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.
August 27, 2008 at 3:37 pm
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
August 28, 2008 at 9:34 am
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