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

Here's a solution that uses the datepart function to account for the fact that your weeks start on Mondays:

with demo_normalized as 
(
 select id,
  start_date,
  (datepart(dw,start_date) + 5) % 7 as test,
  dateadd(d,
       0 - ((datepart(dw,start_date) + 5) % 7),
       start_date
  ) as start_date_firstofweek,
  dateadd(d,
       6 - ((datepart(dw,start_date) + 5) % 7),
       start_date
  ) as start_date_lastofweek,
  end_date,
  dateadd(d,
       0 - ((datepart(dw,end_date) + 5) % 7),
       end_date
  ) as end_date_firstofweek,
  dateadd(d,
       6 - ((datepart(dw,end_date) + 5) % 7),
       end_date
  ) as end_date_lastofweek,
  datediff(week,
      dateadd(d,
          0 - ((datepart(dw,start_date) + 5) % 7),
          start_date
      ),
      dateadd(d,
          6 - ((datepart(dw,end_date) + 5) % 7),
          end_date
      )
  ) as no_of_weeks
 from demo
),
demo_cte as
(
  select
    id,
    dateadd(day,7,start_date_firstofweek) as start_date,
    dateadd(day,7,start_date_lastofweek) as end_date,
    end_date_firstofweek,
    no_of_weeks   
  from demo_normalized
  where no_of_weeks >= 3
  UNION ALL select
    id,
    dateadd(day,7,start_date) as start_date,
    dateadd(day,7,end_date) as end_date,
    end_date_firstofweek,
    no_of_weeks   
  from demo_cte
  where
    (dateadd(day,8,start_date) < end_date_firstofweek)
),
demo_union as
(
  select id, start_date, end_date, no_of_weeks from demo_normalized where no_of_weeks = 1
  union all
  select id, start_date, start_date_lastofweek as end_date, no_of_weeks
  from demo_normalized where no_of_weeks >= 2  
  union all
  select id, start_date, end_date, no_of_weeks from demo_cte
  union all
  select id, end_date_firstofweek as start_date, end_date, no_of_weeks
  from demo_normalized where no_of_weeks >= 2  
)
select
  d0.id,
 d0.no_of_weeks,
 convert(varchar, d0.start_date, 106) as start_date,
 convert(varchar, d0.end_date, 106) as end_date
from demo_union d0
order by d0.id, d0.start_date

EDIT (added CTE for the weeks in between): Here is the link to sqlfiddle.

Note: This solution requires no additional DDL - no additional entities have to be created and maintained. In short, it doesn't reinvent the Calendar. All the calendar logic is contained in the query.

Answer 2

Enjoy!

WITH D AS (
SELECT id
     , start_date
     , end_date
     , start_date AS WEEK_START
     , start_date + 7 - DATEPART(weekday,start_date) + 1
       AS week_end
 FROM DATA
), W AS (
SELECT id
     , start_date
     , end_date
     , WEEK_START
     , WEEK_END
 FROM D
UNION ALL
SELECT id
     , start_date
     , end_date
     , WEEK_END + 1 AS WEEK_START
     , WEEK_END + 7 AS WEEK_END
 FROM W
WHERE WEEK_END < END_DATE
)
SELECT ID
     , WEEK_START AS START_DATE
     , WEEK_END AS END_DATE
 FROM W
 ORDER BY 1, 2;

Answer 3

set datefirst 1
GO

with cte as (
    select ID, Start_date, End_date, Start_date as Week_Start_Date, (case when datepart(weekday, Start_date) = 7 then Start_Date else cast(null as datetime) end) as Week_End_Date, datepart(weekday, Start_date) as start_weekday, cast(0 as int) as week_id
    from (
        values (1, cast('25-Apr-2011' as datetime), cast('8-May-2011' as datetime)),
                (2, cast('23-Apr-2011' as datetime), cast('27-May-2011' as datetime))
    ) t(ID, Start_date, End_date)

    union all

    select ID, Start_date, End_date, dateadd(day, 1, Week_Start_date) as Week_Start_Date, (case when start_weekday + 1 = 7 then dateadd(day, 1, Week_Start_date) else null end) as Week_End_date, (case when start_weekday = 7 then 1 else start_weekday + 1 end) as start_weekday, (case when start_weekday = 7 then week_id + 1 else week_id end) as week_id
    from cte
    where Week_Start_Date != End_date
)
select ID, min(Week_Start_Date), isnull(max(Week_End_Date), max(End_Date))
from cte
group by ID, Week_id
order by ID, 2
option (maxrecursion 0)

If you wanted to get the number of weeks, you could change the select after the cte to be:

select ID, Start_date, End_date, count(distinct week_id) as Number_Of_Weeks
from cte
group by ID, Start_date, End_date
option (maxrecursion 0)

Obviously to change the data used, the anchor (first part) of the cte where values() is being used would be changed.

This uses Monday as the first day of the week. To us a different day, change the set datefirst statement at the top - -- http://msdn.microsoft.com/en-gb/library/ms181598.aspx

Answer 4

You should use a date tally table similar to the type that Jeff Moden suggests in The "Numbers" or "Tally" Table: What it is and how it replaces a loop (required login).

A Tally table is nothing more than a table with a single column of very well indexed sequential numbers starting at 0 or 1 (mine start at 1) and going up to some number. The largest number in the Tally table should not be just some arbitrary choice. It should be based on what you think you'll use it for. I split VARCHAR(8000)'s with mine, so it has to be at least 8000 numbers. Since I occasionally need to generate 30 years of dates, I keep most of my production Tally tables at 11,000 or more which is more than 365.25 days times 30 years.

I started with Tony's SQL Fiddler but implemented a DateInformation table to be a little more generic. This could be something that you could reuse.

--Build Test Data, For production set the date 
--range large enough to handle all cases.
CREATE TABLE DateInformation (
    [Date] date,
    WeekDayNumber int,
)

--From Tony
CREATE TABLE Sample_Data (
id int,
start_date date,
end_date date )

DECLARE @CurrentDate Date = '2010-12-27'

While @CurrentDate < '2014-12-31'
BEGIN
    INSERT DateInformation VALUES (@CurrentDate,DatePart(dw,@CurrentDate))
    SET @CurrentDate = DATEADD(DAY,1,@CurrentDate)
END

--From Tony
INSERT Sample_Data VALUES (1, '2011-04-25','2011-05-08')
INSERT Sample_Data VALUES (2, '2011-04-23','2011-05-27')

Here's the solution using CTE to join the sample data to the DateInformation table.

--Solution using CTE
with Week (WeekStart,WeekEnd) as
(
  select d.Date 
        ,dateadd(day,6,d.date) as WeekEnd
  from DateInformation d
  where d.WeekDayNumber = 2
)

select 
  s.ID
  ,case when s.Start_date > w.WeekStart then s.Start_Date 
    else w.WeekStart end as Start_Date
  ,case when s.End_Date < w.WeekEnd then s.End_Date
    else w.WeekEnd end as End_Date
from Sample_Data s
join Week w on w.WeekStart > dateadd(day,-6,s.start_date)
            and w.WeekEnd <= dateadd(day,6,s.end_date);

See solution in SQL Fiddle

Answer 5

try this query, hope it will work

If your week starts on Sunday, use below

set datefirst 7

declare @FromDate datetime = '20130110'
declare @ToDate datetime = '20130206'

select datepart(week, @ToDate) - datepart(week, @FromDate) + 1

If your week starts on Monday, use below

set datefirst 1

declare @FromDate datetime = '20100201'
declare @ToDate datetime = '20100228'

select datepart(week, @ToDate) - datepart(week, @FromDate) + 1

note: both query will yield to diffrent results, since their starting dates differ.

Answer 6

setting ambient test

    declare @dt table (ID int,Start_date datetime,
                       End_date datetime,No_Of_Weeks int)

    insert into @dt (ID,Start_date,End_date,No_Of_Weeks)
    select 1,   '25-Apr-11',   '8-May-11',     2
    union all
    select 2,  '23-Apr-11' ,  '27-MAy-11' ,   6;

try this...

    with cte as (select d.ID
                       ,d.Start_date
                       ,(select MIN([end]) from (values(d.End_date),(DATEADD(day,-1,DATEADD(week,DATEDIFF(week,0,d.Start_date)+1,0))))V([end])) as End_date
                       ,d.End_date as end_of_period
                 from @dt d
                 union all select d.ID
                      ,DATEADD(day,1,d.End_date) as Start_date
                       , case when d.end_of_period < DATEADD(week,1,d.End_date) then d.end_of_period else DATEADD(week,1,d.End_date) end as End_date
                       ,d.end_of_period as end_of_period
                 from cte d
                 where end_of_period <> End_date
                )
    select ID
          ,cast(Start_date as DATE) Start_date
          ,cast(End_date as date) End_date 
    from cte
    order by cte.ID,cte.Start_date
    option(maxrecursion 0)

the resultset achieved...

    ID  Start_date  End_date
    1   2011-04-25  2011-05-01
    1   2011-05-02  2011-05-08
    2   2011-04-23  2011-04-24
    2   2011-04-30  2011-05-01
    2   2011-05-07  2011-05-08
    2   2011-05-14  2011-05-15
    2   2011-05-21  2011-05-22
    2   2011-05-28  2011-05-27

Answer 7

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

Answer 8

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 9

You should look at using the DATEDIFF function.

I'm not sure what you're asking for in the second part of your question, but once you've got the difference between the dates, you may want to have a look at using CASE on the result of your DATEDIFF.