SELECT START_date, END_DATE , START_date || ' To '|| END_DATE P3riod
FROM
(
select decode((trim(to_char(period,'DAY'))), 'MONDAY', period,period) START_date,
decode((trim(to_char(period,'DAY'))), 'SUNDAY', period ,period + 6) END_date
from
(
select next_day(trunc(:P_DATE-7), 'Monday')+(7*(level-1)) PERIOD
from dual
connect by level <=1000
union all
select next_day(trunc(:P_DATE), 'Sunday')+(7*(level-1)) period
from dual
connect by level <=1000
order by 1
)
where PERIOD <= :T_DATE
)
WHERE START_date <> END_date
FROM
(
select decode((trim(to_char(period,'DAY'))), 'MONDAY', period,period) START_date,
decode((trim(to_char(period,'DAY'))), 'SUNDAY', period ,period + 6) END_date
from
(
select next_day(trunc(:P_DATE-7), 'Monday')+(7*(level-1)) PERIOD
from dual
connect by level <=1000
union all
select next_day(trunc(:P_DATE), 'Sunday')+(7*(level-1)) period
from dual
connect by level <=1000
order by 1
)
where PERIOD <= :T_DATE
)
WHERE START_date <> END_date
No comments:
Post a Comment