I've got some sql code which lists all the days within the last 4 weeks of the report run date.
I now need to add another field in which calculates the week number (in the year). The to_char(d, 'ww') function doesnt quite fit my requirements - i need a week to run from Mon-Sun, not Sat-Fri.
Fig1. is the current output (using the code below), Fig2. is the desired output.
Any help with the code is appreciated output.xls
SELECT d asm_date, TO_CHAR(d, 'Day') day_of_week, to_char(d, 'ww') week_no
FROM (SELECT NEXT_DAY(TRUNC(SYSDATE - 1), 'Sunday') - LEVEL + 1 d
CONNECT BY LEVEL <= 28)
ORDER BY 1