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