So you have a query that only returns a few dates from your period, but you need to show all of them?
In that case you need a data source containing all the dates you need to see. So your process
- Create a table with all the dates in your period
- Left join your query to this on date
- Nvl/coalesce the sum to display zero when your query returns no rows
In the example below the sales table only includes rows for some days in January. So I've generated a table of dates on the fly. Then left joined to it. You could replace this with a real dates table if you want (useful if you need to exclude non-working or other days for whatever reason):
select time_id, sum(nvl(amount_sold, 0)) from SH.SALES s
where trunc(time_id, 'mm') = date'1998-01-01'
group by time_id
order by time_id;
TIME_ID SUM(NVL(AMOUNT_SOLD,0))
02-JAN-1998 00:00:00 798
03-JAN-1998 00:00:00 1,264
05-JAN-1998 00:00:00 306
06-JAN-1998 00:00:00 378
09-JAN-1998 00:00:00 5,100
10-JAN-1998 00:00:00 1,580
12-JAN-1998 00:00:00 1,092
14-JAN-1998 00:00:00 1,746
16-JAN-1998 00:00:00 308
20-JAN-1998 00:00:00 742
23-JAN-1998 00:00:00 312
28-JAN-1998 00:00:00 4,680
29-JAN-1998 00:00:00 546
with dates as (
select date'1997-12-31'+level dt from dual
connect by level <= 31
)
select dt, sum(nvl(amount_sold, 0)) from dates d
left join SH.SALES s
on d.dt = s.time_id
and trunc(time_id, 'mm') = date'1998-01-01'
group by dt
order by dt;
DT SUM(NVL(AMOUNT_SOLD,0))
01-JAN-1998 00:00:00 0
02-JAN-1998 00:00:00 798
03-JAN-1998 00:00:00 1,264
04-JAN-1998 00:00:00 0
05-JAN-1998 00:00:00 306
06-JAN-1998 00:00:00 378
07-JAN-1998 00:00:00 0
08-JAN-1998 00:00:00 0
09-JAN-1998 00:00:00 5,100
10-JAN-1998 00:00:00 1,580
11-JAN-1998 00:00:00 0
12-JAN-1998 00:00:00 1,092
13-JAN-1998 00:00:00 0
14-JAN-1998 00:00:00 1,746
15-JAN-1998 00:00:00 0
16-JAN-1998 00:00:00 308
17-JAN-1998 00:00:00 0
18-JAN-1998 00:00:00 0
19-JAN-1998 00:00:00 0
20-JAN-1998 00:00:00 742
21-JAN-1998 00:00:00 0
22-JAN-1998 00:00:00 0
23-JAN-1998 00:00:00 312
24-JAN-1998 00:00:00 0
25-JAN-1998 00:00:00 0
26-JAN-1998 00:00:00 0
27-JAN-1998 00:00:00 0
28-JAN-1998 00:00:00 4,680
29-JAN-1998 00:00:00 546
30-JAN-1998 00:00:00 0
31-JAN-1998 00:00:00 0