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?

3 answers

  • answered 2018-11-14 10:22 Caius Jard

    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/YYYY as 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

  • answered 2018-11-14 10:32 George Joseph

    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
    

  • answered 2018-11-14 10:32 Barbaros Özhan

    If you want to perform this for the current year, you may use the following SQL statement which uses such a RIGHT JOIN as 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 52 weeks, true but truncated :). So, I used 53,

    Notice that select to_char( date'2016-12-31' - 7 / 24, 'ww') from dual yields 53 as a sample.

    Rextester Demo