Split data between 2 dates in SQL

Question

I have 2 date columns called Start_date and End_date in my table. I need to first find out how many weeks are in between those 2 dates and split the data.

--For e.g. if data is as given below,

ID  Start_date  End_date   No_Of_Weeks
1   25-Apr-11   8-May-11     2
2   23-Apr-11   27-May-11    6

--I need the result like this:

ID  Start_date       End_date

1       25-Apr-2011     01-May-2011
1       02-May-2011     08-May-2011  

2       23-Apr-2011     24-Apr-2011
2       25-Apr-2011     01-Apr-2011
2       02-May-2011     08-May-2011
2       09-May-2011     15-May-2011
2       16-May-2011     22-May-2011
2       23-May-2011     27-May-2011

Please help me out with the query. My week start date is Monday.

Answer 1

You can use a Calendar table defining then weeks and join it to your data.

I've created a sql fiddle for the following:

CREATE TABLE Calendar_Weeks (
  week_start_date date,
  week_end_date date )

CREATE TABLE Sample_Data (
  id int,
  start_date date,
  end_date date )

INSERT Calendar_Weeks (week_start_date, week_end_date) VALUES ('2011-04-18','2011-04-24')
INSERT Calendar_Weeks (week_start_date, week_end_date) VALUES ('2011-04-25','2011-05-01')
INSERT Calendar_Weeks (week_start_date, week_end_date) VALUES ('2011-05-02','2011-05-08')
INSERT Calendar_Weeks (week_start_date, week_end_date) VALUES ('2011-05-09','2011-05-15')
INSERT Calendar_Weeks (week_start_date, week_end_date) VALUES ('2011-05-16','2011-05-22')
INSERT Calendar_Weeks (week_start_date, week_end_date) VALUES ('2011-05-23','2011-05-29')

INSERT Sample_Data (id, start_date, end_date) VALUES (1, '2011-04-25','2011-05-08')
INSERT Sample_Data (id, start_date, end_date) VALUES (2, '2011-04-23','2011-05-27')


SELECT id, week_start_date, week_end_date
FROM Sample_Data CROSS JOIN Calendar_Weeks
WHERE week_start_date BETWEEN start_date AND end_date
UNION
SELECT id, week_start_date, week_end_date
FROM Sample_Data CROSS JOIN Calendar_Weeks
WHERE week_end_date BETWEEN start_date AND end_date

I have to admit the UNION of the queries feels a bit of a hack to include rows at the start or end of the set, so you might prefer to use Ravi Singh's solution.

You can also use INNER JOIN if you like:

SELECT id, week_start_date, week_end_date
FROM Sample_Data INNER JOIN Calendar_Weeks
ON week_start_date BETWEEN start_date AND end_date
UNION
SELECT id, week_start_date, week_end_date
FROM Sample_Data INNER JOIN Calendar_Weeks
ON week_end_date BETWEEN start_date AND end_date

Answer 2

As per the last understanding, this will work :

with demo_cte as 
(select id,
 start_date,
 dateadd(day,6,DATEADD(wk, DATEDIFF(wk,0,start_date), 0)) end_date,
 end_date last_end_date,
 no_of_weeks no_of_weeks from demo


 union all

 select id,dateadd(day,1,end_date),
   dateadd(day,7,end_date),

 last_end_date
 ,no_of_weeks-1 from demo_cte

 where no_of_weeks-1>0)

 select id, start_date,
case
when end_date<=last_end_date then end_date
else
last_end_date
end
end_date
from demo_cte order by id,no_of_weeks desc

SQL Fiddle

And if number of weeks is not available use this :

with demo_cte as 
(select id,
 start_date,
 dateadd(day,6,DATEADD(wk, DATEDIFF(wk,0,start_date), 0)) end_date,
 end_date last_end_date
 --,no_of_weeks no_of_weeks
 from demo


 union all

 select id,dateadd(day,1,end_date),
   dateadd(day,7,end_date),

 last_end_date
 --,no_of_weeks-1 
 from demo_cte

 where --no_of_weeks-1>0
 dateadd(day,7,end_date)<=last_end_date 
)

 select id, start_date,
case
when end_date<=last_end_date then end_date
else
last_end_date
end
end_date
from demo_cte order by id,start_date
--,no_of_weeks desc