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 Ship

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 >= '05/25/2012' and sd <= '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.

StackExchange Database Administrators Q#18579, answer score: 3