• From the article, you wrote " Tally table .. starting at 0 or 1 (mine start at 1)"

    There are advantages for having the Tally table include 0 and the "Freight by Date" problem becomes easier if 0 is included.

    The original SQL solution contains:

    SELECT t.N-1+@DateStart AS ShippedDate

    FROM dbo.Tally t

    WHERE t.N-1+@DateStart <= @DateEnd

    This SQL snippet has "N - 1", which derives the tally to start with 0. Wouldl it not be simplier to have the tally table already contain 0?

    The original SQL solution has a problem that if the tally table does have a row for zero, the results are different (there is an extra day before the oldest shipment).

    The original SQL solution has been modified to eliminate all variables so that the solution can be used in a view, does not assume that the tally table has 0 but allows the tally table to contain 0:

    SELECT dates.ShippedDate

    , COALESCE( SUM(o.Freight) , 0) AS TotalFreight

    FROM dbo.Orders o

    RIGHT OUTER JOIN

    (SELECT t.N - 1 + Shipments.DateStart AS ShippedDate

    FROM dbo.Tally t

    JOIN(SELECTMIN(ShippedDate) AS DateStart

    ,MAX(ShippedDate) AS DateEnd

    FROMdbo.Orders

    ) AS Shipments

    ON t.N BETWEEN 1 AND ( Shipments.DateEnd - Shipments.DateStart + 1 )

    ) AS dates

    ON o.ShippedDate = dates.ShippedDate

    GROUP BY dates.ShippedDate

    ORDER BY dates.ShippedDate

    Here is revised SQL that assumes that the tally table has 0. The solution is slight simplier with the original SQL as comments and bold.

    SELECT dates.ShippedDate

    , COALESCE( SUM(o.Freight) , 0) AS TotalFreight

    FROM dbo.Orders o

    RIGHT OUTER JOIN

    -- (SELECT t.N - 1 + Shipments.DateStart AS ShippedDate

    (SELECT t.N + Shipments.DateStart AS ShippedDate

    FROMdbo.Tally t

    JOIN(SELECT MIN(ShippedDate) AS DateStart

    ,MAX(ShippedDate) AS DateEnd

    FROMdbo.Orders

    ) AS Shipments

    -- ON t.N BETWEEN 1 AND ( Shipments.DateEnd - Shipments.DateStart + 1 )

    ON t.N BETWEEN 0 AND (Shipments.DateEnd - Shipments.DateStart )

    ) AS dates

    ON o.ShippedDate = dates.ShippedDate

    GROUP BY dates.ShippedDate

    ORDER BY dates.ShippedDate

    SQL = Scarcely Qualifies as a Language