I'm trying to create a table that has start and end columns by week that dont overlap month transitions. Using January 2016 as an example, I want the results to look like:
Start End
1/1/2016 1/2/2016
1/3/2016 1/9/2016
1/10/2016 1/16/2016
1/17/2016 1/23/2016
1/24/2016 1/30/2016
1/31/2016 1/31/2016
What I'm currently getting with the query is (I want the records in the 2nd and 3rd columns to line up accordingly):
DATES Wk_START_END MONTH_START_END
1/1/2016 1/1/2016
1/2/2016 1/2/2016
1/3/2016 1/3/2016
1/4/2016
1/5/2016
1/6/2016
1/7/2016
1/8/2016
1/9/2016 1/9/2016
1/10/2016 1/10/2016
1/11/2016
1/12/2016
1/13/2016
1/14/2016
1/15/2016
1/16/2016 1/16/2016
1/17/2016 1/17/2016
1/18/2016
1/19/2016
1/20/2016
1/21/2016
1/22/2016
1/23/2016 1/23/2016
1/24/2016 1/24/2016
1/25/2016
1/26/2016
1/27/2016
1/28/2016
1/29/2016
1/30/2016 1/30/2016
1/31/2016 1/31/2016 1/31/2016
Here's the query at the moment:
SELECT trunc
(sysdate, 'YEAR')+rownum-1 DATES
--,to_char(trunc(sysdate,'YEAR') + rownum -1 ,'D') Day_Of_Wk
, CASE
WHEN to_char
(trunc
(sysdate, 'YEAR')+rownum-1, 'D') = '1' THEN trunc
(sysdate, 'YEAR')+rownum-1
WHEN to_char
(trunc
(sysdate, 'YEAR')+rownum-1, 'D') = '7' THEN trunc
(sysdate, 'YEAR')+rownum-1
ELSE NULL
END Wk_Start_End
, CASE
WHEN trunc
(sysdate, 'YEAR')+rownum-1 = TRUNC
(trunc
(sysdate, 'YEAR')+rownum-1, 'MONTH') THEN trunc
(sysdate, 'YEAR')+rownum-1
WHEN trunc
(sysdate, 'YEAR')+rownum-1 = Add_months
(TRUNC
(trunc
(sysdate, 'YEAR')+rownum-1, 'MONTH'), 1)-1 THEN trunc
(sysdate, 'YEAR')+rownum-1
END Month_Start_end
FROM all_objects
WHERE trunc
(sysdate, 'YEAR')+rownum <= Add_months
(trunc
(sysdate, 'YEAR'), 12)-1;
Any help is appreciated. Thanks!
The query below starts from scratch - it doesn't use any of your code (or its output). The year and month are hard-coded in the first CTE (subfactored query in the WITH clause at the top); more likely in your application you will exclude the first CTE, named inputs, and you will make y and m into bind variables in the definition of first_date (also in the WITH clause).
I used your convention: the week starts on "day 1 of the week" (which in the U.S. is Sunday) and ends on "day 7 of the week." This can be adjusted through NLS parameters if needed.
with
inputs ( y, m ) as (
select 2016, 1 from dual
),
first_date ( f_dt ) as (
select to_date(to_char(y, '0009') || '-' || to_char(m, '09'), 'yyyy-mm')
from inputs
),
mth_dates ( dt ) as (
select f_dt + level - 1 from first_date
connect by level <= last_day(f_dt) - f_dt + 1
),
start_dates ( dt, rn ) as (
select dt, row_number() over (order by dt)
from ( select dt from mth_dates where to_char(dt, 'd') = '1'
union
select min(dt) from mth_dates )
),
end_dates ( dt, rn ) as (
select dt, row_number() over (order by dt)
from ( select dt from mth_dates where to_char(dt, 'd') = '7'
union
select max(dt) from mth_dates )
)
select s.rn as week_nbr, s.dt as start_date, e.dt as end_date
from start_dates s inner join end_dates e on s.rn = e.rn;
WEEK_NBR START_DATE END_DATE
---------- ---------- ----------
1 2016-01-01 2016-01-02
2 2016-01-03 2016-01-09
3 2016-01-10 2016-01-16
4 2016-01-17 2016-01-23
5 2016-01-24 2016-01-30
6 2016-01-31 2016-01-31
ADDED at OP's request:
To generate the start and end dates for the entire year one can use the query below.
with
inputs ( y ) as (
select 2016 from dual
),
first_date ( f_dt ) as (
select to_date(to_char(y, '0009') || '-01-01', 'yyyy-mm-dd')
from inputs
),
year_dates ( dt ) as (
select f_dt + level - 1 from first_date
connect by level <= add_months(f_dt, 12) - f_dt
),
start_dates ( dt, rn ) as (
select dt, row_number() over (order by dt)
from ( select dt from year_dates where to_char(dt, 'd') = '1'
or extract(day from dt) = 1 )
),
end_dates ( dt, rn ) as (
select dt, row_number() over (order by dt)
from ( select dt from year_dates where to_char(dt, 'd') = '7'
or extract(day from dt + 1) = 1 )
)
select s.dt as start_date, e.dt as end_date
from start_dates s inner join end_dates e on s.rn = e.rn;
Further comment: I actually like Matthew's answer better than mine. His solution simply groups the days into the proper "set intersections" of months and weeks and uses max() and min() over those groups, avoiding the need for a join. It's a better solution than mine.
For completeness, I reproduce Matthew's solution below, with a few minor changes.
First, to match the requirement (and as Matthew suggested), I add 1 to dte in forming the groups by week, so weeks begin on Sundays and end on Saturdays.
Second, as I suggested in a comment to Matthew's Answer, I use "month" and "week" directly to form the groups; there is no need for dense_rank().
Third, to conform with good coding practices, I added an explicit date format model to to_date() in the first CTE.
Credit: @Matthew McPeak
with dtes ( dte ) as (
select to_date ('01-Jan-2016', 'dd-Mon-yyyy') + rownum - 1
from dual
connect by rownum <= 366 -- 2016 is a leap year
),
dtes_grouped_by_month_week ( dte, mth, wk ) as (
select dte, to_char(dte, 'mm'), to_char(dte+1, 'iw')
from dtes
)
select min(dte) start_date, max(dte) end_date
from dtes_grouped_by_month_week
group by mth, wk
order by start_date;