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
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