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
Advertisement
Answer
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 intendSUM(bottles)
?- Converting a column to a
date
and then to adatetime
is also suspicious. It is unclear why you would want adatetime
for the first column.