I have one requirement
START_DATE : 03/01/2018
END_DATE : 31/01/2018
I need a query which will list all the weeks starting date and end date between these two dates like this
StartWeek EndWeek
03/01/2018 04/01/2018
07/01/2018 11/01/2018
14/01/2018 18/01/2018
21/01/2018 25/01/2018
28/01/2018 31/01/2018
Here working days are from Sunday to thursday. Week starts from sunday
Try this,
CREATE OR REPLACE PACKAGE week_pkg
AS
TYPE week_rec IS RECORD (start_week DATE, end_week DATE);
TYPE week_tab IS TABLE OF week_rec;
FUNCTION get_weeks (p_start_date DATE, p_end_date DATE) RETURN week_tab PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY week_pkg
AS
FUNCTION get_weeks (p_start_date DATE, p_end_date DATE) RETURN week_tab PIPELINED
IS
v_date DATE;
v_week_rec week_rec;
BEGIN
v_date := p_start_date - 7;
LOOP
v_week_rec.start_week := NEXT_DAY(v_date, 'SUNDAY');
IF v_week_rec.start_week < p_start_date THEN
v_week_rec.start_week:= p_start_date;
END IF;
v_week_rec.end_week := NEXT_DAY(v_date, 'THURSDAY');
IF v_week_rec.end_week >= p_end_date THEN
v_week_rec.end_week := p_end_date;
PIPE ROW (v_week_rec);
EXIT;
ELSIF v_week_rec.end_week <= p_start_date THEN
v_week_rec.end_week := NEXT_DAY(v_week_rec.start_week, 'THURSDAY');
END IF;
v_date := v_week_rec.end_week;
PIPE ROW (v_week_rec);
END LOOP;
END;
END;
/
SELECT *
FROM table(week_pkg.get_weeks(to_date('03-JAN-2018', 'DD-MON-YYYY'), to_date('31-JAN-2018', 'DD-MON-YYYY')));
Output:
START_WEEK END_WEEK
---------- ---------
03-JAN-18 04-JAN-18
07-JAN-18 11-JAN-18
14-JAN-18 18-JAN-18
21-JAN-18 25-JAN-18
28-JAN-18 31-JAN-18
You could use this SQL.
WITH t (START_DATE, END_DATE)
AS (SELECT TO_DATE ('03/01/2018', 'dd/mm/yyyy'),
TO_DATE ('31/01/2018', 'dd/mm/yyyy')
FROM DUAL)
SELECT DISTINCT
CASE
WHEN LEVEL = 1
THEN
START_DATE
ELSE
CASE
WHEN TRUNC (START_DATE + LEVEL + 7, 'DAY') > END_DATE
THEN
TRUNC (START_DATE + LEVEL, 'DAY')
ELSE
TRUNC (START_DATE + LEVEL + 7, 'DAY')
END
END
START_DATE,
CASE
WHEN NEXT_DAY (
CASE
WHEN LEVEL = 1 THEN START_DATE
ELSE TRUNC (START_DATE + LEVEL + 7, 'DAY')
END,
'THURSDAY') > END_DATE
THEN
END_DATE
ELSE
NEXT_DAY (
CASE
WHEN LEVEL = 1
THEN
CASE
WHEN TRIM (TO_CHAR (START_DATE, 'DAY')) = 'THURSDAY'
THEN
START_DATE - 7
ELSE
START_DATE
END
ELSE
TRUNC (START_DATE + LEVEL + 7, 'DAY')
END,
'THURSDAY')
END
END_DATE
FROM DUAL CROSS JOIN t
CONNECT BY LEVEL < END_DATE - START_DATE;
You can use a recursive sub-query factoring clause:
WITH input_dates ( start_date, end_date ) AS (
SELECT DATE '2018-01-03', DATE '2018-01-31'
FROM DUAL
),
valid_start_date ( start_date, end_date ) AS (
SELECT CASE
WHEN start_date - TRUNC( start_date, 'IW' )
IN (
0, -- Monday
1, -- Tuesday
2, -- Wednesday
3, -- Thursday
6 -- Sunday
)
THEN start_date
ELSE NEXT_DAY( start_date, 'SUNDAY' )
END,
end_date
FROM input_dates
),
dates ( start_week, end_week, end_date ) AS (
SELECT start_date,
LEAST( NEXT_DAY( start_date, 'THURSDAY' ), end_date ),
end_date
FROM valid_start_date
WHERE start_date <= end_date
UNION ALL
SELECT NEXT_DAY( start_week, 'SUNDAY' ),
LEAST( end_week + INTERVAL '7' DAY, end_date ),
end_date
FROM dates
WHERE NEXT_DAY( start_week, 'SUNDAY' ) <= end_date
)
SELECT start_week, end_week
FROM dates;
Output:
START_DATE END_DATE
---------- ----------
2018-01-03 2018-01-04
2018-01-07 2018-01-11
2018-01-14 2018-01-18
2018-01-21 2018-01-25
2018-01-28 2018-01-31
Something like that: Oracle 11 and higher (maybe Oracle 9 and 10 too...)
If Your week begins with Sunday, maybe You should subtract 1 day in the inner sql. ("select trunc(dt, 'IW')-1 as beg_period" for week beginning with Sunday -instead of "select trunc(dt, 'IW') as beg_period" for week beginning with Monday)
with periods as
(
select greatest(to_date(&date_from, 'dd.mm.yyyy'), beg_period) beg_period -- date_from or first day of week
, least(to_date(&date_to, 'dd.mm.yyyy'), lead(beg_period) over (order by beg_period)-1 ) end_period -- date_to or last day of week (and =NULL if week begins AFTER date_to)
from
(select trunc(dt, 'IW')-1 as beg_period -- calc the beginning of week for every day generated, taking week beginning in Sunday ("-1")
from
(-- generate all the days between &date_from and FIRST DAY OF NEXT WEEK JUST AFTER &date_to (including)
select to_date(&date_from, 'dd.mm.yyyy') + (level-1) as dt
from dual
connect by level <= (trunc(to_date(&date_to, 'dd.mm.yyyy'), 'IW') + 7 -- also calc the first day of next week just AFTER date_to; it needed
-- for lead(...) in further (just to calc end-of-week for date_to, by lead(...))
- to_date(&date_from, 'dd.mm.yyyy')
+ 1
)
)
group by trunc(dt, 'IW') -- take only 1 beg_period for every week (may use "group by", or "distinct")
)
)
select beg_period
, end_period - case to_char(end_period, 'DAY','NLS_DATE_LANGUAGE=''numeric date language''') when '7' then 2 when '6' then 1 else 0 end
as end_period -- correction for weekend (Friday and Saturday) for week beginning in Sunday
from periods
where end_period is not null -- exclude next week just after date_to (which has end_period=NULL)
order by 1
;
See the last "case": it depends on NLS. In my expression, I mean Saturday='7' and Friday='6' (I guess it's correct if week begins with Sunday).
Elegant solution
SELECT DISTINCT TO_CHAR(z.START_DATE,'MMRRRR') MM
,TO_CHAR(z.START_DATE,'DD-MON-RRRR') START_DATE
,TO_CHAR(z.END_DATE,'DD-MON-RRRR') END_DATE
FROM (WITH C_RANGE AS
(SELECT TRUNC(:STARTDATE) + LEVEL - 1 FECHA_INI
,ADD_MONTHS(TRUNC(:STARTDATE),LEVEL) - 1 FECHA_FIN
FROM DUAL
CONNECT BY LEVEL <= (TRUNC(:ENDDATE) - TRUNC(:STARTDATE)))
SELECT DISTINCT
(CASE WHEN L.FECHA_INI = TRUNC(:STARTDATE) THEN TRUNC(:STARTDATE) ELSE ADD_MONTHS(L.FECHA_FIN,-1) +1 END) START_DATE
,(CASE WHEN L.FECHA_FIN +1 >= TRUNC(:ENDDATE) THEN TRUNC(:ENDDATE) ELSE L.FECHA_FIN END) END_DATE
FROM C_RANGE L
WHERE ADD_MONTHS(L.FECHA_FIN,-1) <= TRUNC(:ENDDATE)
AND TRUNC(:ENDDATE) >= TRUNC(:STARTDATE)) z
ORDER BY MM ASC ;
