I'm trying to find a way to include all months in a result set
my query looks like this:
select to_char(changedate, 'YY-Month') Mon,
sum(downtime) dt
from (select * from assetstatus
where assetnum in ('x','y','z')
and siteid = 'xyz'
and changedate between to_date('10/1/2019','mm/dd/yy')
and to_date('9/30/2020','mm/dd/yy')
and exists (select 1 from workorder where worktype = 'RD'
and workorder.wonum = assetstatus.wonum
and workorder.siteid = assetstatus.siteid))
group by to_char(changedate, 'YY-Month'), to_char(changedate, 'YY-Mm')
order by to_char(changedate, 'YY-Mm');
but if i'm missing a month of data say December 2019 where no downtime was reported then it will return
19-October 91.85
19-November 26.55
20-January 0
20-February 29.1999999999999987
20-March 32.133333333333333666
20-April 1664.4333333333333
20-May 246.95
20-June 12.25
20-July 2.1
20-August 0
20-September 3
So I'm looking to have it include 19-December with 0 hours if nothing is present.
The dates are chosen dynamically based on user input one being a year prior to the later date.
using 12c drivers if anyone has any thoughts I'd greatly appreciate it!
Best Answer
It is a calendar you need. How to get it? Using a row generator technique.
As I don't have your table, I'll use Scott's
EMP
and count employees hired in every month. Calendar I'm going to create will contain months between MIN and MAXhiredate
(you can create any dates you want, of course).EMP
table's contents:Query that does the job: