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

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.

  • 2
    Is this homework from a course or did you make this scenario up yourself?
    – user507
    May 30 '12 at 13:29
  • @ShawnMelton - Absolutely not. This is not a homework assignment,and I'm not in school. I tried to make this real-world question as generic as possible and read the way a word problem would read. I guess in that regard it worked.
    – mg1075
    May 30 '12 at 13:41
  • Would appreciate knowing what the downvote was for... If this is a bad question, let me know why it's so bad. Does the question not make sense?
    – mg1075
    May 30 '12 at 14:06
  • It is best if you explain as such, that you generalized the question to leave out the innocent or what have you. If it looks like homework folks are generally reluctant to help on SE.
    – user507
    May 30 '12 at 17:48
  • 1
    @ShawnMelton - I've updated the question in an attempt to make the expected output clearer.
    – mg1075
    May 30 '12 at 20:27

I'd add a PriorShipments field to the fact table. You'll have a lot of rows, but clustering on the ShipDate field should be pretty efficient, and querying would be quite simple.

ShipDate    DATE NOT NULL,
PriorShipments  INT NOT NULL DEFAULT 0
INSERT INTO #Shipments (ShipDate, ID, CustomerID) VALUES
    ('2005-08-10',   7112,  942), 
    ('2007-07-15',   8798,  160), 
    ('2009-04-03',   8798,  160), 
    ('2009-04-15',   8798,  160), 
    ('2009-04-21',   8798,  160), 
    ('2009-04-21', 145751,  139), 
    ('2009-04-22',   7112,  942), 
    ('2009-04-22',  12121, 1015), 
    ('2009-04-25',   8798,  160), 
    ('2009-05-12',   8798,  160)

SET PriorShipments = NumPrev
FROM #Shipments AS RS
        SELECT S1.ID, S1.ShipDate, COUNT(*) AS NumPrev
        FROM #Shipments AS S1
            INNER JOIN #Shipments AS S2 ON S1.ID = S2.ID AND S2.ShipDate < S1.ShipDate
        GROUP BY S1.ID, S1.ShipDate
        ) AS Seq ON RS.ID = Seq.ID AND RS.ShipDate = Seq.ShipDate

-- If Farmer Brown gets a Dept of Agriculture grant to upgrade his database, he could instead use:
SET PriorShipments = Seq - 1
FROM #Shipments AS RS
        FROM #Shipments
        ) AS Seq ON RS.ID = Seq.ID AND RS.ShipDate = Seq.ShipDate

-- The good part
SELECT COUNT(*) FROM #Shipments WHERE ShipDate BETWEEN '2009-04-01' AND '2009-04-30'
SELECT COUNT(*) FROM #Shipments WHERE ShipDate BETWEEN '2009-04-01' AND '2009-04-30' AND PriorShipments = 1
SELECT COUNT(*) FROM #Shipments WHERE ShipDate BETWEEN '2009-04-01' AND '2009-04-30' AND PriorShipments >= 2

You can pre-aggregate this a little, as long as Farmer Brown is never going to care about to which farms he has been shipping peaches.

Movin' to the country, gunna eat me a lot of peaches...

  • Farmer Brown is old-fashioned and still goes with SQL Server 2000.
    – mg1075
    May 30 '12 at 23:23
  • Sorry, I missed that. Edited to accomodate SQL 2000; it's uglier, but works. May 30 '12 at 23:34
  • Cool beans - er, peaches; I think I can roll with this approach for building the necessary aggregates. Thanks much.
    – mg1075
    May 31 '12 at 19:22

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');

   , 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.

  • The aggregating I get, but is there an efficient way to do this so as not to query the million-plus records Shipments table? Hope I've made that clearer by updating the end of the question ("The issue").
    – mg1075
    May 30 '12 at 22:49
  • 1
    You could build an index on the date. sqlfiddle.com/#!3/70002
    – swasheck
    May 30 '12 at 23:07
  • One million+ records is not insanely large and an index would allow the engine to find the data more readily.
    – swasheck
    May 30 '12 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.