Get data each day and show 0 if no data

oracleoracle-11g

Hi i'm trying to create query that will give me the employee double break count each day.
I need to display each day but what i able to display now is the only days that has value.

This is the same question as my old question but mark as duplicate but i did not answer my question
so i post new question

 SELECT   shift, count (COUNT) AS COUNT, TRUNC (date_time) AS date_time
              FROM vw_break_time_doublebreak
             WHERE TRUNC (date_time) IN (
                      SELECT       TRUNC (TO_DATE ('2020-01-01', 'YYYY-MM-DD'),
                                          'MM'
                                         )
                                 + LEVEL
                                 - 1 AS dateshift
                            FROM DUAL
                      CONNECT BY LEVEL <= 31)

          GROUP BY shift, TRUNC (date_time)
          ORDER BY shift, date_time ASC;

result im getting

SHIFT   COUNT   DATE_TIME

R   11  1/1/2020
R   29  1/2/2020
R   22  1/3/2020
R   3   1/4/2020
R   32  1/6/2020
R   39  1/7/2020
R   41  1/8/2020
R   39  1/9/2020
R   44  1/10/2020
R   9   1/11/2020
R   1   1/12/2020
R   20  1/13/2020
R   29  1/14/2020
R   31  1/15/2020
R   28  1/16/2020
R   34  1/17/2020
R   7   1/18/2020
R   2   1/19/2020
R   33  1/20/2020
R   32  1/21/2020
R   26  1/22/2020
R   37  1/23/2020
R   32  1/24/2020
R   7   1/25/2020
R   41  1/27/2020
R   41  1/28/2020
R   28  1/29/2020
R   41  1/30/2020
R   30  1/31/2020

see result above there's no row for january 5 and january 26.

On the source view there's really no data on that date.

expected result

SHIFT   COUNT   DATE_TIME

    R   11  1/1/2020
    R   29  1/2/2020
    R   22  1/3/2020
    R   3   1/4/2020
    R   0   1/5/2020 <=
    R   32  1/6/2020
    R   39  1/7/2020
    R   41  1/8/2020
    R   39  1/9/2020
    R   44  1/10/2020
    R   9   1/11/2020
    R   1   1/12/2020
    R   20  1/13/2020
    R   29  1/14/2020
    R   31  1/15/2020
    R   28  1/16/2020
    R   34  1/17/2020
    R   7   1/18/2020
    R   2   1/19/2020
    R   33  1/20/2020
    R   32  1/21/2020
    R   26  1/22/2020
    R   37  1/23/2020
    R   32  1/24/2020
    R   7   1/25/2020
    R   0   1/26/2020 <=
    R   41  1/27/2020
    R   41  1/28/2020
    R   28  1/29/2020
    R   41  1/30/2020
    R   30  1/31/2020

Note there is also other shift i only show the data on R shift
Note there is no data on vw_break_time_doublebreak that has date of jan 5 and jan 26
data result will be use in graph using chart js
Hope someone help me out with this thank you.

Best Answer

With the following input data:

SQL> select * from vw_break_time_doublebreak order by date_time;

SHIFT      DATE_TIME
---------- ----------------
R1     01/01/2020 01:00
R1     02/01/2020 01:00
R1     02/01/2020 02:00
R1     04/01/2020 01:00
R1     04/01/2020 02:00
R1     04/01/2020 03:00
R1     04/01/2020 04:00
R2     05/01/2020 01:00
R2     05/01/2020 02:00
R2     05/01/2020 03:00
R3     31/01/2020 01:00
R3     31/01/2020 02:00
R3     31/01/2020 03:00

13 rows selected.

Here is a possible solution:

SQL> --
SQL> with cte as
  2  (
  3   select
  4   to_date('01/01/2020') + level -1 as dateshift
  5   from dual
  6   connect by level <= 31
  7  )
  8  select
  9  nvl(shift,'N/A') as shift,
 10  count(dt) as count,
 11  to_char(trunc(ds),'DD/MM/YYYY') as date_shift
 12  from (
 13   select
 14   trunc(dateshift) as ds,
 15   trunc(date_time) as dt,
 16   shift
 17   from cte
 18   left outer join vw_break_time_doublebreak
 19   on trunc(dateshift) = trunc(date_time)
 20   order by dateshift
 21  )
 22  group by shift, ds
 23  order by ds;

SHIFT       COUNT DATE_SHIFT
---------- ---------- ----------
R1          1 01/01/2020
R1          2 02/01/2020
N/A         0 03/01/2020
R1          4 04/01/2020
R2          3 05/01/2020
N/A         0 06/01/2020
N/A         0 07/01/2020
N/A         0 08/01/2020
N/A         0 09/01/2020
N/A         0 10/01/2020
N/A         0 11/01/2020
N/A         0 12/01/2020
N/A         0 13/01/2020
N/A         0 14/01/2020
N/A         0 15/01/2020
N/A         0 16/01/2020
N/A         0 17/01/2020
N/A         0 18/01/2020
N/A         0 19/01/2020
N/A         0 20/01/2020
N/A         0 21/01/2020
N/A         0 22/01/2020
N/A         0 23/01/2020
N/A         0 24/01/2020
N/A         0 25/01/2020
N/A         0 26/01/2020
N/A         0 27/01/2020
N/A         0 28/01/2020
N/A         0 29/01/2020
N/A         0 30/01/2020
R3          3 31/01/2020

31 rows selected.