Rebuilding a slow daily shipment report

The problem

A manufacturer used a legacy ERP and SQL report builder to review posted U.S. distributor shipments by region. Shipping staff reran the report as shipments posted through the day to track what had gone out. Regional sales managers reran it to see which distributors' shipments had posted, answer status questions, and follow up on shipments still expected that day.

The report took about two minutes to load. Staff often exported it once and worked from that copy, even when more shipments posted later. It also sometimes assigned shipments to the wrong region, and failed outright when territory rules were changed. An operations manager asked me to improve its load time and correct the region assignments.

Understanding the old query

Several employees had revised the report before the company introduced formal change control. Those employees had since left, and documentation was sparse. I began by mapping the roughly 400-line query against report output and ERP records.

The ZIP-code territory rules

The ERP did not store a region on each shipment. The query assigned one with a roughly 74-line CASE expression containing a long series of WHEN conditions. For U.S. shipments, 56 active conditions compared the shipping ZIP against hard-coded ranges.

The expression was copied into all three SQL sections returning product, order-charge, and shipment-charge rows. About 222 repeated lines in a roughly 400-line query. A territory change meant finding the relevant condition and making the same edit three times.

The conditions ran in written order. Each WHEN checked whether the five-digit ZIP fell between two bounds and returned a region if it did; the first match won. A shipper-specific exception required an additional check, and ZIPs matching no condition received a fallback classification.

I took shipments users said were in the wrong region and traced each ZIP and any shipper exception through the conditions. I compared the reported region against the one sales and shipping staff expected, then asked whether the difference reflected a deliberate exception or a rule needing correction. The existing shipper exception turned out to still be needed, so I kept it in the rebuilt logic.

I considered storing a territory directly on every shipment or distributor. Sales and production staff did not want to maintain territory codes while entering operational records. We agreed to keep territory calculated, but to move its rules out of the report SQL.

I created an effective-dated reference table for the ZIP rules. The rebuilt query matched each shipment's five-digit ZIP to a range using an inequality join, and applied the rule in effect on the shipment date. The approved shipper exception remained a separate rule with explicit priority over the ordinary ZIP match.

-- Match a U.S. shipment to the ZIP rule effective on its ship date.
-- Apply the approved shipper exception separately.
SELECT DemoShipments.ShipmentKey, DemoZipTerritoryRules.Territory
FROM DemoShipments
LEFT JOIN DemoZipTerritoryRules
  ON LEFT(DemoShipments.ShipZip, 5)
     BETWEEN DemoZipTerritoryRules.FirstZip
         AND DemoZipTerritoryRules.LastZip
 AND DemoShipments.ActualShipAt >= DemoZipTerritoryRules.ValidFrom
 AND DemoShipments.ActualShipAt <  DemoZipTerritoryRules.ValidTo
WHERE DemoShipments.IsPosted = 1
  AND DemoShipments.ShipCountry = 'US';

Before accepting a ZIP-rule change, I checked for ranges that overlapped in both ZIP and effective dates. This query had to return no rows:

-- Run before publishing territory rules; expect zero rows.
SELECT FirstRange.RuleKey, SecondRange.RuleKey
FROM DemoZipTerritoryRules AS FirstRange
JOIN DemoZipTerritoryRules AS SecondRange
  ON FirstRange.RuleKey  <  SecondRange.RuleKey
 AND FirstRange.FirstZip <= SecondRange.LastZip
 AND SecondRange.FirstZip <= FirstRange.LastZip
 AND FirstRange.ValidFrom < SecondRange.ValidTo
 AND SecondRange.ValidFrom < FirstRange.ValidTo;

I also checked for shipments with no matching range, and for shipments matching more than one. Effective dates preserved the classification used for older shipments while letting a new territory map take effect later. The table had an owner and entered the company's change-control process. Territory changes no longer required edits to three copies of the same calculation.

Making the daily query faster

The old dataset processed the full history of posted shipments even though users requested a single day. I added the report builder's selected date to the dataset query, filtering posted shipments before joining product lines and charges. In SQL Server the report builder supplies that date through @ReportDate:

-- Select the report day before joining product or charge details.
WITH DayShipments AS (
    SELECT ShipmentKey, OrderKey, ActualShipAt, ShipZip
    FROM DemoShipments
    WHERE IsPosted = 1
      AND ShipCountry = 'US'
      AND ActualShipAt >= @ReportDate
      AND ActualShipAt <  DATEADD(day, 1, @ReportDate)
)
SELECT DayShipments.ShipmentKey,
       DemoProductLines.LineKey,
       DemoProductLines.Amount
FROM DayShipments
JOIN DemoProductLines
  ON DemoProductLines.ShipmentKey = DayShipments.ShipmentKey;

The product-line section above illustrates the filter; the order-charge and shipment-charge sections used the same filtered shipment set. The date range includes the start of the selected day and excludes the start of the next, so shipments with recorded times throughout the day stay in the dataset. After the change I checked product and charge rows against ERP records to confirm the filter had not dropped charges belonging to that day.

I added comments to the rebuilt queries for whoever maintains the report next. They identify each row type, explain the selected-day condition and the shipper exception, and state what the overlap check must return.

Delivery and result

I rebuilt the report with a selected-date parameter, regional totals, and shipment-level detail. Users could refresh it during the day, filter to a region, and inspect the posted shipments and charges behind a total. Shipping staff could identify a record needing correction; sales managers could see how distributor activity was assigned across regions.

Median load time fell from about two minutes to about 20 seconds. Measured across ten test dates.

Limiting the dataset to one day addressed the slow load. The territory-table checks addressed classification: a disputed result could be traced to a single rule or exception, and a conflicting rule change could be caught before it reached the report.