Oracle SQL - Fiscal Week of Year-CodePudding

I want to calculate the Fiscal Week of the Year with the following rules:

The fiscal year always starts on June/01
The week always starts on Sunday
Samples are provided for 2019 but ideally it should work for any year

A few samples of correct values are provided with the screenshot attached

I tried to do something like TO_NUMBER(TO_CHAR(TO_DATE(DATE_ID 1,'DD-Mon-YY'),'IW')) -21 but towards the end of the Calendar year I start to get negatives

SELECT
    DATE_ID
    , WEEK_OF_YEAR
FROM DATE_DIM
WHERE
    DATE_ID IN
    (
        20190601
        , 20190602
        , 20190915
        , 20191228
        , 20191229
        , 20200101
        , 20200601
        , 20200606
        , 20200607
    )
ORDER BY DATE_ID ASC
;

CodePudding user response：

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 7),'IW')
  FROM dual

Or in your case,

SELECT date_id,TO_CHAR(ADD_MONTHS(TO_DATE(date_id,'YYYYMMDD'), 7),'IW') week_of_year
  FROM date_dim

If you don't like the ISO dating where week 1 is the week beginning Sunday during which the 1st of the year falls, you can try offsetting it by moving it back to the previous sunday (TRUNC(..,'D'), then advance a week, then add the 7 months. See if this works for you:

SELECT date_id, TO_CHAR(ADD_MONTHS(TRUNC(date_id,'D')   7,7),'IW') week_of_year
  FROM date_dim

CodePudding user response：

You got confused with your date formats ('yyyymmdd' vs. 'DD-Mon-YY'), so I am using a real date (mydate) in my answer. Convert your string or number to a proper date and you are there :-)

The important thing is to check whether your date is >= June 1. Once this is done you can subtract that year's June 1 or the previous year's one. Well, more or less :-)

select 
  mydate,
  to_char(mydate, 'DY') as tag,
  trunc
  (
    case when to_char(mydate, 'mmdd') >= '0601' then
      trunc(mydate   1, 'iw')   6 - to_date(to_char(mydate, 'yyyy') || '0601', 'yyyymmdd')
    else
      trunc(mydate   1, 'iw')   6 - to_date(to_char(extract(year from mydate) - 1) || '0601', 'yyyymmdd')
    end / 7
  )   1 as fiscal_week
from ...
order by mydate;

Demo: https://dbfiddle.uk/N5pX_5cV