you should get the idea & shouldn't be too resource intensive)
UPDATE trucks
SET totalmileage = t.totalmileage + r.mileage
FROM trucks t, (SELECT SUM(Mileage) AS mileage, TruckID FROM
ReportingData GROUP BY TruckID) r
WHERE t.truckid = r.truckid
I'm not sure why you'd need customerID in both Trucks & ReportingData -
I would of thought that it should just be in Trucks?
Anyway, HTH
Alex
-----Original Message-----
From: John mccosker [mailto:[EMAIL PROTECTED]
Sent: 18 May 2004 15:47
To: CF-Talk
Subject: Need SQL Advice
Hi,
I need some advice here,
there are two tables in a database that I am concerned with here.
One is called say dbo.Trucks and the other is dbo.ReportingData.
dbo.Trucks stores all the information regarding all customer trucks,
customers could have one truck, others could have 50. There is a column
named totalMileage [datatype float] within dbo.Trucks.
dbo.Trucks table design (there is other data, but this all I am
concerned with) TRUCKID | CUSTOMERID | TOTALMILEAGE
dbo.ReportingData has each vehicles reporting data. A unit sends a gps
report from the vehicle to our DSN every ten minutes when the vehicle is
moving. So on average there are 100 records per day. The unit on the
vehicle calculates the mileage based on time and speed, performing
calucations every ten seconds, then the mileage done within that 10
minute period is also sent.
dbo.ReportingData
CUSTOMERID | TRUCKID | MILEAGE
What I am looking to do is run a nightly schedule for the day before,
sum all the mileage in dbo.ReportingData and add it to the existing
mileage within dbo.Trucks for each vehicle.
Ideally I want to do this in a stored proc for all customers in the one
run. How ever the only way I know how to sum one table and update the
other is by using a cursor.
However I have read this is resource intensive.
Would anyone have any fabulous suggestions in this instance?
Thanx j.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
