How to fill value as zero when No data exists for particular week in oracle
I have a table with following structure.
Note_title varchar2(100) Note_created_on date
Now in a report, I want to show all notes created week-wise, So I implemented the following solution for it.
SELECT to_char(Note_created_on - 7/24,'ww')||'/'||to_char(Note_created_on - 7/24,'yyyy') as Week , nvl(COUNT(Note_title),'0') as AMOUNT FROM Notes GROUP BY to_char(Note_created_on - 7/24,'ww') , to_char(Note_created_on -7/24,'yyyy') ORDER BY to_char(Note_created_on - 7/24,'ww') DESC
And i am getting correct output from it, But suppose week 42,45 do not have any created Note then its just missing it. Sample Output:
WEEK AMOUNT 46/2018 3 44/2018 22 43/2018 45 41/2018 1 40/2018 2 39/2018 27 38/2018 23
So How can I get zero values for week 42,45 instead of leaving them out?
As mentioned by jarlh:
Create a list of weeks:
SELECT TO_CHAR(LEVEL, 'FM00')||'/2018' wk FROM dual CONNECT BY LEVEL <= 53
This query generates 53 rows, and level is just a number.. 1.. 2.. upto 53. We format it to become 01/2018, 02/2018.. 53/2018
If you plan to use this query in other years, you'd be better off making the year dynamic:
SELECT TO_CHAR(LEVEL, 'FM00')||TO_CHAR(sysdate-7/24,'/YYYY') wk FROM dual CONNECT BY LEVEL <= 53
(Credits to Barbaros for pointing out that the last day of any year is reported by Oracle as being in week 53, or said another way 7*52 = 364)
We left join the notes data onto it. I wasn't really clear on why you subtracted 7 hours from the date (time zone?) but I left it. I removed the complexity of the count, as you seem to only want the count of records in a particular week. I also removed the double to_char, because you can do it all in a single operation. One doesn't need to
TO_CHAR(date, 'WW')||'/'||TO_CHAR(date,'YYYY')etc.. you just tochar with
WW/YYYYas a format. Our query now looks like:
SELECT lst.wk as week, COALESCE(amt, 0) as amount FROM ( SELECT TO_CHAR(LEVEL, 'FM00')||TO_CHAR(sysdate-7/24,'/YYYY') wk FROM dual CONNECT BY LEVEL <= 52 ) lst LEFT OUTER JOIN ( SELECT to_char(Note_created_on - 7/24,'ww/yyyy') as wk, COUNT(*) as amt FROM Notes GROUP BY to_char(Note_created_on - 7/24,'ww/yyyy') ) dat ON lst.wk = dat.wk ORDER BY lst.wk
For weeks where there are no note, the left join records a null against that week, so we coalesce it to make it 0.
You can, of course, do the query in other ways (many ways), here's a compare:
SELECT lst.wk as week, COUNT(dat.wk) as amount FROM ( SELECT TO_CHAR(LEVEL, 'FM00')||TO_CHAR(sysdate-7/24,'/YYYY') wk FROM dual CONNECT BY LEVEL <= 52 ) lst LEFT OUTER JOIN ( SELECT to_char(Note_created_on - 7/24,'ww/yyyy') as wk FROM Notes ) dat ON lst.wk = dat.wk GROUP BY lst.wk ORDER BY lst.wk
In this form we do the groupby/count after the join. By counting the dat.wk, which for some lst.wk might be NULL, we can omit the coalesce, because count(null) is 0
First you would need to generate all the weeks between each year, after that would left join with the Notes tables on the weeks and group by the weeks generated. Eg:
with weeks as ( select level as lvl /*Assume 52 weeks in a calendar year..*/ from dual connect by level <=52 ) ,weeks_year as (select distinct b.lvl||'/'||trunc(Note_created_on,'YYYY') as week_year_val /*From the start of year in Note_created_on*/ from Notes a join weeks b on 1=1 ) SELECT a.week_year_val as Week ,COUNT(Note_title) as AMOUNT FROM weeks_year a LEFT JOIN Notes b ON a.week_year_val=to_char(b.Note_created_on - 7/24,'ww')||'/'||to_char(b.Note_created_on - 7/24,'yyyy') GROUP BY a.week_year_val ORDER BY a.week_year_val DESC
If you want to perform this for the current year, you may use the following SQL statement which uses such a
RIGHT JOINas below :
SELECT d.week as Week, nvl(COUNT(Note_title), '0') as AMOUNT FROM Notes RIGHT JOIN (SELECT lpad(level,2,'0')|| '/' ||to_char(sysdate,'yyyy') as week, '0' as amount FROM dual CONNECT BY level <= 53) d ON ( d.week = to_char(Note_created_on - 7 / 24, 'ww') ||'/'||to_char(Note_created_on - 7 / 24, 'yyyy') ) GROUP BY d.week ORDER BY d.week DESC;
P.S. There's a common belief that a year is composed of
52weeks, true but truncated :). So, I used
select to_char( date'2016-12-31' - 7 / 24, 'ww') from dualyields
53as a sample.