Arch1medes Arch1medes - 4 months ago 15
SQL Question

Remove Nulls from multiple rows; create [Start] and [End] columns

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!

Answer

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;