How would one create my expected results. Any help would be appreciated, Thanks in advance!
Master Calendar:
SELECT DISTINCT
--CA.CALENDAR_DATE,
TO_CHAR(CALENDAR_DATE,'MM/DD/YYYY') AS CALENDAR_DATE
TO_CHAR(NEXT_DAY(CALENDAR_DATE, 'Monday') - 7, 'MM/DD/YY-') ||
TO_CHAR(NEXT_DAY(CALENDAR_DATE, 'Monday') - 1, 'MM/DD/YY') AS WEEK_OF_YEAR,
ROW_NUMBER () OVER ( ORDER BY CALENDAR_DATE) AS MasterCalendar_RNK
FROM CALENDAR CA
WHERE 1=1
--AND CA.CALENDAR_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE), -12) AND TRUNC(SYSDATE)
--AND CA.CALENDAR_DATE BETWEEN TRUNC(SYSDATE) -5 AND TRUNC(SYSDATE)
ORDER BY TO_DATE(CALENDAR_DATE,'MM/DD/YYYY') DESC
Input:
Member StartDate EndDate
A 1/31/17
B 2/1/17 2/15/17
Expected Results:
Member StartDate EndDate Week_Of_Year Active
A 1/31/17 1/30/17-2/5/17 1
A 1/31/17 2/6/17-2/12/17 1
A 1/31/17 2/13/17-2/19/17 1
B 2/1/17 2/15/17 1/30/17/2/5/17 1
B 2/1/17 2/15/17 2/6/17-2/12/17 1
B 2/1/17 2/15/17 2/13/17-2/19/17 1
