Update Query 3

[NotSQL]

Can someone help me? I am having problems updating a table with the correct figure using the query below.

Update Master_Data_Vehicle
Set Actual_Annual_KM = (Mileage / (DateDiff(Day, First_Registration, RepairDate)))*365
From MileageTemp inner Join Master_Data_Vehicle
On MileageTemp.ChassisNo = Master_Data_Vehicle.Chassis_Number
Where Mileage <> 0 And (DateDiff(Day, First_Registration, RepairDate)) <> 0
and RepairDate <= Contract_End


I have two tables. One looks like this

ChassisNo OrderNo Mileage RepairDate ContractEnd
WMAL20ZZZ2Y0 111522 222393 25/01/2007 05/03/2007
WMAL20ZZZ2Y0 125485 242061 03/10/2007 05/03/2008
WMAL20ZZZ2Y0 112548 233865 20/08/2007 05/09/2007

the other looks like this

ChassisNo RegistrationDate
WMAL20ZZZ2Y0 06/03/2002

what i want to do is find out the annual km per year using the calculation above but only using the most recent repair date. Can someone tell what im doing wrong with my update query

 

[davejam]

you could try a

SELECT TOP 1 ***rest of your query*** ORDER BY RepairDate DESC

as one possibility??

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[davejam]

Sorry that needs more work... i'm just looking at it....

if you create a query to pull only the latest record for a specific chassis then you can use that in the update...

back in a tick with something

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[davejam]

try something along the lines of

Update Master_Data_Vehicle
Set Actual_Annual_KM = 
(
SELECT TOP 1 (b.Mileage / (DateDiff(Day, b.First_Registration, b.RepairDate)))*365
FROM MileageTemp B 
WHERE b.Mileage <> 0 
 AND (DateDiff(Day, b.First_Registration, b.RepairDate)) <> 0
 AND b.RepairDate <= b.Contract_End 
 AND b.ChassisNo = Master_Data_Vehicle.ChassisNo
ORDER BY RepairDate DESC
)

may need tweaking a little

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[Gixonita]

How about this?

UPDATE	Master_Data_Vehicle

SET	Actual_Annual_KM = (MilageTemp.Mileage / (DATEDIFF (day, First_Registration, MilageTemp.RepairDate) / 365.0))

FROM	MileageTemp 

INNER JOIN	Master_Data_Vehicle
	ON	Master_Data_Vehicle.Chassis_Number = MileageTemp.ChassisNo 

WHERE	MilageTemp.OrderNo = (SELECT	MAX (B.OrderNo)
			      FROM	MilageTemp AS B
			      WHERE	B.ChassisNo = MilageTemp.ChassisNo)

 

[NotSQL]

I tweaked it around but still isnt giving me the result i need.

Update Master_Data_Vehicle
Set Actual_Annual_KM =
(
SELECT TOP 1 (b.Mileage / (DateDiff(Day, a.First_Registration, b.RepairDate)))*365
FROM MileageTemp B, Master_Data_Vehicle a
WHERE b.Mileage <> 0
AND (DateDiff(Day, a.First_Registration, b.RepairDate)) <> 0
AND b.RepairDate <= b.Contract_End
AND b.ChassisNo = a.Chassis_Number
ORDER BY RepairDate DESC
)

I not a 100% sure TOP is correct thing to use in this Scenario as im trying to use the rusult of the calculation based on the most recent repair date. I could also have 1000's of different chassisNumbers

 

[gmmastros]

It appears as though the code posted by Gixonita will produce the correct results for you.

Another thing you need to consider is integer math. Your original code, and the code from davejam does not accomodate integer math, which can have a rather large impact on the results. With integer math the result it 43070. When you properly accommodate the math, the result is 43373 km/yr. That's a big difference.

I wrote another version of this query that is different than Gixonita, but produces the same results. I'll explain...

First, I created some table variables so I could test the code. I also added another vehicle so that I could make sure the code accommodates multiple vehicles. The data I used is this...

SET DATEFORMAT DMY

Declare @MileageTemp Table(ChassisNo VarChar(20), OrderNo Int, Mileage Int, RepairDate DateTime, ContractEnd DateTime)

Insert Into @MileageTemp Values('WMAL20ZZZ2Y0',111522,222393,'25/01/2007','05/03/2007')
Insert Into @MileageTemp Values('WMAL20ZZZ2Y0',125485,242061,'03/10/2007','05/03/2008')
Insert Into @MileageTemp Values('WMAL20ZZZ2Y0',112548,233865,'20/08/2007','05/09/2007')
Insert Into @MileageTemp Values('ABC',112548,1000000,'20/08/2007','05/09/2007')

Declare @Master_Data_Vehicle Table(ChassisNo VarChar(20), First_Registration DateTime, Actual_Annual_KM int)
Insert Into @Master_Data_Vehicle Values('WMAL20ZZZ2Y0','06/03/2002', NULL)
Insert Into @Master_Data_Vehicle Values('ABC','06/03/2002', NULL)

Now, let's build this in pieces so that it's easier to understand. Since you want to use the mileage from the greatest repair date, let's first figure out what that date is for each vehicle. Copy/paste the test data above and the code below in to a query analyzer window.

          Select ChassisNo, Max(RepairDate) As RepairDate
          From   @MileageTemp
          Group By ChassisNo

You'll see that you get the chassis number and the max repair date for each vehicle.

Now, we're going to use the above query as a derived table to get the rest of the data from the MileageTemp table.

Select  M.ChassisNo,
        M.RepairDate,
        M.Mileage
From    @MileageTemp M
        Inner Join [!]([/!]
          [blue]Select ChassisNo, Max(RepairDate) As RepairDate
          From   @MileageTemp
          Group By ChassisNo[/blue]
          [!]) As A
          On  M.ChassisNo = A.ChassisNo
          And M.RepairDate = A.RepairDate[/!]

Notice the part in blue. That's exactly the same as the first query. The parts in red show you how to join that back to the same table, but with certain conditions applied (the ON clause). This method of creating a query and joining it to another is called a derived table. This is a very powerful concept, that gives you a lot of flexibility and performs very well. I encourage you to study this and become familiar with it. Once you get this concept, you will be able to write better queries that perform a lot faster. Your database will thank you.

You'll see now that you get the chassis number, repair date and mileage for the rows that you want to use in the calculations. The rest is fairly simple. Simply join to the master vehicle data table to get the first registration date and use that in your calculations. Like this...

Select  M.ChassisNo,
        M.RepairDate,
        M.Mileage,
        1.0 * M.Mileage / DateDiff(Day, MDV.First_Registration, A.RepairDate) * 365
From    @MileageTemp M
        Inner Join (
          Select ChassisNo, Max(RepairDate) As RepairDate
          From   @MileageTemp
          Group By ChassisNo
          ) As A
          On  M.ChassisNo = A.ChassisNo
          And M.RepairDate = A.RepairDate
        Inner Join @Master_Data_Vehicle MDV
          On M.ChassisNo = MDV.ChassisNo

Finally, create the update statement.

Update	MDV
Set     Actual_Annual_KM = 1.0 * M.Mileage / DateDiff(Day, MDV.First_Registration, A.RepairDate) * 365.25
From    @MileageTemp M
        Inner Join (
          Select ChassisNo, Max(RepairDate) As RepairDate
          From   @MileageTemp
          Group By ChassisNo
          ) As A
          On  M.ChassisNo = A.ChassisNo
          And M.RepairDate = A.RepairDate
        Inner Join @Master_Data_Vehicle MDV
          On M.ChassisNo = MDV.ChassisNo

Does this make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NotSQL]

Thank you so Gixonita and gmmastros both your examples work perfectly. I thank you gmmastros for you in depth explanation, it has been very useful in understanding how you achieved this result..

Thanks

 

[Gixonita]

No prob, glad I could help