The background (masking a real-world problem behind a word problem)
Farmer Brown has a peach orchard in California and ships hundreds of thousands of peaches all across the country. When a shipment of peaches reaches its destination, the deliveryman inspects the peaches and keeps all the peaches that fail his inspection.
When the peaches that failed inspection arrive back at Farmer Brown's farm, the peach defects are repaired at Farmer Brown's Peach Hospital and get re-shipped with the next batch of peach shipments. And if the original peaches fail inspection again, they go through the same process, as long as the peach is salvageable.
Peach Hospitals are expensive, and so is the cost of shipping, so Farmer Brown starts thinking, "For the peaches I shipped within any time range, how many total did I ship? Of that total, how many shipped for the 2nd time? for the 3rd (or more) time?”
At a high-level, the peach shipments tracking table (on a SQL Server 2000 database) has 9 million+ records with different kinds of shipments, and looks (grossly) akin to:
ShipmentDate PeachID Shipment Type 8/10/2005 7112 Shipment to Customer 7/15/2007 8798 Shipment to Customer 4/3/2009 8798 Shipment to Customer 4/15/2009 8798 Shipment to Customer 4/21/2009 8798 Shipment to Customer 4/21/2009 145751 Shipment to Customer 4/22/2009 7112 Shipment to Customer 4/22/2009 12121 Shipment to Customer 4/25/2009 8798 Shipment to Customer 5/12/2009 8798 Shipment to Customer
If we attempted to return a sample of what Farmer Brown was looking for, it would be results like this:
given the following Parameters:
StartDate = 4/1/2009, EndDate = 4/30/2009
-- The easy part
Total Peaches Shipped to Customer: 100,000
(PeachID 8798 contributes 4 to the total. PeachID 7112 contributes 1 to the total.)
-- The messy part
Total Peaches Shipped for the 2nd time: 5,000
(PeachID 8798 contributes 1 to the total. PeachID 7112 contributes 1 to the total.)
Total Peaches Shipped for the 3rd (or more) time: 800
(PeachID 8798 contributes 2 to the total.)
What would be the most efficient way of building a holding table(s) of this data (refreshed once a day - real-time is not important), so that when retreiving Farmer Brown's requested data, the data can be returned speedily? I'm thinking that if the shipments starting from the beginning of Farmer Brown's opening day to present were aggregated by day into a holding table(s), then performing an aggregate on whatever the date range parameters are, the proc will be run faster than querying the Shipments table and its millions of records.
Apologies if I'm not explaining the situation clearly enough. I'd really appreciate an outside perspective on this.