0

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!

5
  • 3
    Does this answer your question? How to insert all the dates in a date range into Oracle database?
    – mustaccio
    Nov 2, 2020 at 20:10
  • Hi thanks for the link - sadly no that doesn't answer it, i'm not trying to put data into the database I'm looking to extract from the db
    – Clyde
    Nov 2, 2020 at 20:32
  • I don't fully understand your question, but I do see some sloppiness, 'to_date('10/1/2019','mm/dd/yy') ; You are - correctly - supplying a 4-digit year in your string to be converted to date but only specifying 2-digit year in your format mask. Fortunately (?) oracle is rather forgiving at this point. But it still a sloppy habit, and this kind of sloppiness could bite you in the future. Also, I'm always suspicious of examples with hard-coded values, wondering if the real case is a bit different.
    – EdStevens
    Nov 2, 2020 at 21:33
  • thanks Ed good feedback - the hard-coded values are a bit more dynamic in the real case. i took a debug print out of the query and you're right about the yy 2 digit vs 4 digit year i'll correct that in the query thank you
    – Clyde
    Nov 2, 2020 at 21:38
  • "the hard-coded values are a bit more dynamic in the real case", Then, to get an optimal solution, it would help to know how those dates really come from and how their values get to the query. For instance, if they come from some other table and are a DATE data type, you would need to account for the time component inherent in oracle DATEs. Not accounting for that is probably the most common cause of "date" comparisons not working as expected.
    – EdStevens
    Nov 3, 2020 at 0:20

1 Answer 1

1

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 MAX hiredate (you can create any dates you want, of course).

EMP table's contents:

SQL> select empno, ename, hiredate
  2  from emp
  3  order by hiredate;

     EMPNO ENAME      HIREDATE
---------- ---------- ----------
      7369 SMITH      17.12.1980
      7499 ALLEN      20.02.1981
      7521 WARD       22.02.1981
      7566 JONES      02.04.1981
      7698 BLAKE      01.05.1981
      7782 CLARK      09.06.1981
      7844 TURNER     08.09.1981
      7654 MARTIN     28.09.1981
      7839 KING       17.11.1981
      7900 JAMES      03.12.1981
      7902 FORD       03.12.1981
      7934 MILLER     23.01.1982
      7788 SCOTT      09.12.1982
      7876 ADAMS      12.01.1983

14 rows selected.

SQL>

Query that does the job:

SQL> with
  2  -- MIN and MAX hiredate
  3  minimax as
  4    (select min(trunc(hiredate, 'mm')) mindat,
  5            max(trunc(hiredate, 'mm')) maxdat
  6     from emp
  7    ),
  8  calendar (datum) as
  9  -- calendar, based on MIN and MAX hiredate
 10    (select add_months(mindat, level)
 11     from minimax
 12     connect by level <= months_between(maxdat, mindat)
 13    )
 14  -- the final result
 15  select
 16    to_char(c.datum, 'yyyy-Month', 'nls_date_language = english') datum,
 17    count(e.empno) cnt
 18  from calendar c left join emp e on trunc(e.hiredate, 'mm') = trunc(c.datum, 'mm')
 19  group by c.datum
 20  order by c.datum;

DATUM                                            CNT
----------------------------------------- ----------
1981-January                                       0
1981-February                                      2
1981-March                                         0
1981-April                                         1
1981-May                                           1
1981-June                                          1
1981-July                                          0
1981-August                                        0
1981-September                                     2
1981-October                                       0
1981-November                                      1
1981-December                                      2
1982-January                                       1
1982-February                                      0
1982-March                                         0
1982-April                                         0
1982-May                                           0
1982-June                                          0
1982-July                                          0
1982-August                                        0
1982-September                                     0
1982-October                                       0
1982-November                                      0
1982-December                                      1
1983-January                                       1

25 rows selected.

SQL>
2
  • Thank you! this gets me really close, what happens when i want to filter employee name emp.name = 'MARTIN'
    – Clyde
    Nov 2, 2020 at 22:14
  • You're welcome. As of MARTIN, it depends on what you want to get as a result. If you want to get the same output as previously, then you'd from calendar c left join emp e on trunc(e.hiredate, 'mm') = trunc(c.datum, 'mm') and e.ENAME = 'MARTIN'. If you want to get only one row (as there's only one date when MARTIN was hired), then you'd from calendar c left join emp e on trunc(e.hiredate, 'mm') = trunc(c.datum, 'mm') where e.ename = 'MARTIN'.
    – Littlefoot
    Nov 3, 2020 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.