Problem

Need to create a result set that summarizes daily totals for a range of dates and need to include those dates with no activity.

Solution

Try this recursive SQL expression to generate a temporary result set with one row for every day of the month, which can then be used as the basis for a query that summarizes daily totals.

SQL

with CALENDAR_MONTH(CALENDAR_DATE) AS(
SELECT date(CALENDAR_DATE)
FROM (values('&&YR-&&MN' concat '-01')) x(CALENDAR_DATE)
UNION ALL
SELECT CALENDAR_DATE+1 day
FROM CALENDAR_MONTH
WHERE month(CALENDAR_DATE+1 day)=month(CALENDAR_DATE))
select *
from CALENDAR_MONTH

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: March 07, 2019