Show all results in date range replacing null records with zero

Question

I am querying the counts of logs that appear on particular days. However on some days, no log records I'm searching for are recorded. How can I set count to 0 for these days and return a result with the full set of dates in a date range?

SELECT r.LogCreateDate, r.Docs
FROM(
    SELECT SUM(TO_NUMBER(REPLACE(ld.log_detail, 'Total Documents:' , ''))) AS Docs, to_char(l.log_create_date,'YYYY-MM-DD') AS LogCreateDate
    FROM iwbe_log l
    LEFT JOIN iwbe_log_detail ld ON ld.log_id = l.log_id
    HAVING to_char(l.log_create_date , 'YYYY-MM-DD') BETWEEN '2020-01-01' AND '2020-01-07'
    GROUP BY to_char(l.log_create_date,'YYYY-MM-DD')
    ORDER BY to_char(l.log_create_date,'YYYY-MM-DD') DESC
    ) r
ORDER BY r.logcreatedate

Current Result - Id like to include the 01, 04, 05 with 0 docs.

LOGCREATEDATE	Docs
2020-01-02	7
2020-01-03	3
2020-01-06	6
2020-01-07	1

Answer 1

You need a full list of dates first, then outer join the log data to that. There are several ways to generate the list of dates but now common table expressions (cte) are an ANSI standard way to do this, like so:

with cte (dt) as (
    select to_date('2020-01-01','yyyy-mm-dd') as dt from dual -- start date here
    union all
    select dt + 1 from cte
    where dt + 1 < to_date('2020-02-01','yyyy-mm-dd') -- finish (the day before) date here
    )
select to_char(cte.dt,'yyyy-mm-dd') as LogCreateDate
     , r.Docs 
from cte
left join (
    SELECT SUM(TO_NUMBER(REPLACE(ld.log_detail, 'Total Documents:' , ''))) AS Docs
         , trunc(l.log_create_date) AS LogCreateDate
    FROM iwbe_log l
    LEFT JOIN iwbe_log_detail ld ON ld.log_id = l.log_id
    HAVING trunc(l.log_create_date) BETWEEN to_date('2020-01-01','yyyy-mm-dd' AND to_date('2020-01-07','yyyy-mm-dd')
    GROUP BY trunc(l.log_create_date)
    ) r on cte.dt = r.log_create_date
order by cte.dt

also, when dealing with dates I prefer to not convert them to strings until final output which allows you to still get proper date order and maximum query efficiency.