querying a table of events for reporting 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

Results
-- 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.)

The issue
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.
I'm not sure how you're going to be reporting this data back to said farmer, here's a quick way of doing what you're requesting: 


declare @test table (
    sd datetime, 
    id int,
    st nvarchar(100)
);

insert @test (sd,id,st) values ('05/25/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/26/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',1,'Shipment to Customer');
insert @test (sd,id,st) values ('05/26/2012',2,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',3,'Shipment to Customer');
insert @test (sd,id,st) values ('05/14/2012',2,'Shipment to Customer');
insert @test (sd,id,st) values ('05/30/2012',5,'Shipment to Customer');


select 
   id
   , count(*) ShippedById
from @test
where sd &gt;= '05/25/2012' and sd &lt;= '05/28/2012'
group by id
compute sum(count(*));


(with emphasis on the select statement)

You will get the number of times a peachid shipped, by id and then you'll get a second set that gives you the total number of peaches shipped.

If you're going to be using SSRS, you can take out the compute clause and then use SSRS to sum the results of ShippedById. Such a solution would also work with Excel.