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.
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
data result will be use in graph using chart js Hope someone help me out with this thank you.
