SQL Query to Count Daily Totals Skips Days

Question

This query counts the amount of bottles we produce over a month and groups them by day. If there are no bottles produces that day then it is skipped from the output instead of returning 0 bottles produced. How can I return with the day's timestamp if there are no bottles produced? I heard the calendar table has to be used for this.

SELECT CONVERT(datetime,CAST(t_stamp AS DATE)), COUNT(bottles) AS 'Good Bottles'
FROM bottles
WHERE t_stamp
    BETWEEN "any date"
    AND "any date"
GROUP BY CAST(t_stamp AS DATE)
ORDER BY CAST(t_stamp AS DATE) ASC

Current Output:

Aug 12, 2019 12:00 am..................4302
Aug 13, 2019 12:00 am..................2302
Aug 17, 2019 12:00 am..................1302
Aug 18, 2019 12:00 am..................4302

Desired Output:

Aug 12, 2019 12:00 am..................4302
Aug 13, 2019 12:00 am..................2302
Aug 14, 2019 12:00 am..................0
Aug 15, 2019 12:00 am..................0
Aug 16, 2019 12:00 am..................0
Aug 17, 2019 12:00 am..................1302
Aug 18, 2019 12:00 am..................4302

Answer 1

You need to generate the days. A pretty simple method uses a recursive CTE:

WITH dates as (
      SELECT CONVERT(date, "any date1") as dte
      UNION ALL
      SELECT DATEADD(day, 1, dte)
      FROM dates
      WHERE dte < "any date2"
     )
SELECT d.dte, COUNT(bottles) AS GoodBottles
FROM dates d LEFT JOIN
     bottles b
     ON CAST(t_stamp as DATE) = d.dte
GROUP BY d.dte
ORDER BY d.dte ASC;

Notes:

• If you have a calendar or tally table, then use that instead.
• If the number of dates exceed 100, you need to add OPTION (MAXRECURSION 0).
• COUNT(bottles) looks suspicious. Do you really intend SUM(bottles)?
• Converting a column to a date and then to a datetime is also suspicious. It is unclear why you would want a datetime for the first column.