I am trying to calculate the Recycle Week. It is either an A or B week. My first attempt was the following with obvious problems as the first day of the week changes every year.
SELECT DECODE(MOD(TO_NUMBER(TO_CHAR(SYSDATE, 'WW')), 2), 0, 'A', 'B') "Recycle Week"
FROM dual
;
My next attempt utilized the ISO week format with better results, although the week changes on Monday.
SELECT DECODE(MOD(TO_NUMBER(TO_CHAR(SYSDATE, 'IW')), 2), 0, 'A', 'B') "Recycle Week"
FROM dual
;
To change to Sunday I shifted the date by one.
SELECT DECODE(MOD(TO_NUMBER(TO_CHAR(SYSDATE+1, 'IW')), 2), 0, 'A', 'B') "Recycle Week"
FROM dual
;
Here is the problem. This works fine until the first week following a 53 week year and continues to be off until the first week following the next 53 week year. Is there a solution using only date math to solve this or do I need a table to identify years that correspond the recycle week A to even or odd weeks?
Additionally, the recycle weeks alternate or toggle every week regardless of year. In other words, it never resets at the beginning of the year. Also, currently even weeks constitute an A week and odd weeks constitute a B week. This becomes a problem once we get to a year with 53 weeks. At that time you will get a pattern of A, B, A, B, new year B, A, B, having two B weeks in a row with week 53 and week 1 both calculating to a B week.
Thanks for your assistance.
"In other words, it never resets at the beginning of the year."
means that at some point in the past, there was a Jan 1st which was either A or B, and ever since that moment, we have toggled week on week. There must have been a *starting* point from which all weeks must be derived.
So I'll assume that
a) your week *starts* on a sunday (you never explicitly specified that but I'm inferring that from your details
b) that Sunday, Jan 1st, 1995 was recycle "A".
From that, we can use simple division to map out every week from then until whenever:
SQL> with
2 starting_point as
3 ( select date '1995-01-01' commence from dual )
4 select
5 commence+rownum-1 dte,
6 to_char(commence+rownum-1,'DY') dy,
7 decode( mod(trunc((rownum-1)/7),2),0,'A',1,'B') rec
8 from dual,
9 starting_point
10 connect by level <= 100;
DTE DY R
--------- --- -
01-JAN-95 SUN A
02-JAN-95 MON A
03-JAN-95 TUE A
04-JAN-95 WED A
05-JAN-95 THU A
06-JAN-95 FRI A
07-JAN-95 SAT A
08-JAN-95 SUN B
09-JAN-95 MON B
10-JAN-95 TUE B
11-JAN-95 WED B
12-JAN-95 THU B
13-JAN-95 FRI B
14-JAN-95 SAT B
15-JAN-95 SUN A
16-JAN-95 MON A
17-JAN-95 TUE A
18-JAN-95 WED A
19-JAN-95 THU A
...
Thus for any nominated date, it would be:
SQL> with
2 starting_point as
3 ( select date '1995-01-01' commence from dual ),
4 nominated_date as
5 ( select date '2016-11-27' nom from dual )
6 select
7 nom,
8 to_char(nom,'DY') dy,
9 decode( mod(trunc((nom-commence)/7),2),0,'A',1,'B') rec
10 from nominated_date,
11 starting_point;
NOM DY R
--------- --- -
27-NOV-16 SUN B