Filling in missing dates DB2 SQL

问题:

My initial query looks like this:

select process_date, count(*) batchCount
from T1.log_comments 
order by process_date asc;

I need to be able to do some quick analysis for weekends that are missing, but wanted to know if there was a quick way to fill in the missing dates not present in process_date.

I've seen the solution here but am curious if there's any magic hidden in db2 that could do this with only a minor modification to my original query.

回答1:

Note: Not tested, framed it based on my exposure to SQL Server/Oracle. I guess this gives you the idea though: *now amended and tested on DB2*

WITH MaxDateQry(MaxDate) AS 
(
    SELECT MAX(process_date) FROM T1.log_comments 
),
MinDateQry(MinDate) AS 
(
    SELECT MIN(process_date) FROM T1.log_comments 
),
DatesData(ProcessDate) AS 
(
    SELECT MinDate from MinDateQry
    UNION ALL
    SELECT (ProcessDate  + 1 DAY) FROM DatesData WHERE  ProcessDate < (SELECT MaxDate FROM MaxDateQry)
)
SELECT  a.ProcessDate, b.batchCount
  FROM  DatesData a LEFT JOIN 
    (
        SELECT process_date, COUNT(*) batchCount
        FROM T1.log_comments 
    ) b
    ON a.ProcessDate = b.process_date
ORDER BY a.ProcessDate ASC;