SalvageDog (2/21/2014)

---

I too am not a fan of the NextDay column. In this age of the DATE data type, why not convert SaleDate to DATE and equate it to the calendar table date? The query plans are roughly the same, and it makes the join a bit simpler.

SELECT

CD.DayDate, CD.NameOfDay

, COUNT(S.SaleID) AS NumberOfSales

, SUM(ISNULL(S.SaleAmt, 0)) AS DaySales

FROM CalDay CD

LEFT JOIN Sales S ON

CONVERT(DATE, S.SaleDate) = CD.DayDate

AND S.SlsRepID IN (0, 3)

WHERE CD.DayID BETWEEN @FirstDay And @FirstDay + 6

GROUP BY CD.DayDate, CD.NameOfDay

ORDER BY CD.DayDate;