In the interest of the first one to post on the forum and because I'm having a tough time wrapping my head around this one, here goes:

I have a table of "container inspection records", that holds a date of inspection, a container ID, and a weight. Every month, each container is weighed and a new inspection record entered...Until the container is full and shipped off elsewhere.

I need to determine the total amount we put into all containers in a specific time period. So for each container, I need to find the first inspection record after @startDate, and the last inspection record before @endDate, subtract them, and add the results of all those operations together to get one number.

I can do it using lots of subqueries in the where statement, but I know there's got to be a better/more efficient way.

I'm using SQL server 2000, and .NET on the client side.

InspectionRecord {Inspection Id, ContainerId, InspectionDate, Weight }

TIA,
-SN

How about a temporary data base with container id, beginning weight, ending weight.

I don't have anything against doing a temp table, but what SQL query can I use to fill it with the data you specified? That's where I'm getting stuck.

There are about 48,000 records in the inspection table and 14,000 containers, and this isn't a performance critical app, but I would like to keep the query time <= 20 seconds.

Thanks for the response,
-SN

SN sorry I can't give you an SQL solution, but a database type programming solution should require only a single pass through the inspection table, assuming there is an index by container and date. For each container id find/read for the first record on or after the start date. Save the weight, find/read the record that is before or on the end date, subtract the previous saved weight from this weight and save as running total. Continue reading and repeat for next container id. The running total will give the total weight added. No record is retained for the of weight of each container. This is old tech way of just serial reading the file from beginning to end and extracting data in range. I think you can do it in 20 seconds (2400 records/second) in a 1GHz. machine since this is a read only application.

Hmm, to fill a temp table use this query;

SELECT MAX(b.InspectionDate) AS MX, MIN(b.InspectionDate) AS MN, (MX-MN) AS DIFF
FROM InspectionRecord b
WHERE b.InspectionDate > @StartDate
AND b.InspectionDate < @EndDate
AND b.ContainerId = @ContainerId

This will return your time difference for each barrel (DIFF), so you just need to loop through each barrel ID and INSERT the value into your temp table, which you can then SUM.

Make sure you have an index on InspectionDate!

Hello all-
Thanks for the responses.

@jhunt-
Your query will give me the difference in dates, but I actually need the difference between the weights that correspond to those dates.

@wizard-fred
I think I may end up going this route...It gives me some insertion points where I can inject some business logic on the subtraction, which may be necessary depending on how messy the data is.

I came up with something that I hope works, since it's pretty simple and fast. I joined the inspection table with itself on container id, grouped it by container ID, and selected the max difference between the two weights in the resulting table. This is taking advantage of the (hopeful) fact that containers' weights can only increase with time.

Select ContainerId, Max(I2.Weight - I1.Weight) FROM Inspection I1 INNER JOIN Inspection I2 ON I1.ContainerID = I2.ContainerID
Where I1.Date <= @endDate And
I2.Date <= @endDate And
I1.Date >= @startDate And
I2.Date >= @startDate
Group By (I1.ContainerId)
ORDER BY (I1.ContainerID)

Under the assumptions that containers' weights only increase with time, can anybody see any bugs with this? I know I said at the beginning that I felt there had to be a simple solution, but this seems too simple to be correct.

In case anybody's curious, these containers mostly house hazardous waste, as this is part of a waste management system for a large power company.

Thanks for the help,
SN