Split data between two dates in SQL

As per the latest 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 the number of weeks is not available then 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

      

        
        
        
      

    

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 queries will give different results as their start dates are different.

You should look at the DATEDIFF function .

I'm not sure what you are asking for in the second part of your question, but once you have the difference between the dates, you can take a look at using CASE in the result of your DATEDIFF.

Here's a solution using 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 (CTE added over several weeks): Here is a link to the sqlfiddle .

Note. This solution does not require additional DDL - no additional entities need to be created or maintained. In short, he does not invent the calendar. All calendar logic is contained in the request.

You should use a date table table similar to the type suggested by Jeff Moden in the "Numbers" or "Tally" table: what it is and how it replaces a loop (login required).

A tally table is nothing more than a single column table with very well indexed sequential numbers starting at 0 or 1 (initial start at 1) and approaching some number. The largest number in the tally should be more than just a random choice. It should be based on what you think you will use it for. I have split VARCHAR (8000) with mine, so it should be at least 8000 numbers. Since I sometimes need to create 30 year dates, I keep most of my Tallys at 11,000 or more, which is over 365.25 days every 30 years.

I started with Tony SQL Fiddler, but implemented the table DateInformation

to be more generic. It might 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 a solution using CTE to combine sample data into a table DateInformation

.

--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

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 want to get the number of weeks, you can change the selection after the cte looks like this:

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 the () values ​​are used will be changed.

It uses Monday as the first day of the week. For us another day change the operator set datefirst

at the top - - http://msdn.microsoft.com/en-gb/library/ms181598.aspx