Spliting date periods in SQl

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi All,
we are having a date range period from 2016-01-02 to 2016-01-30
there are date periods saved in @premiumtable .
I need to split the date range using the date ranges in @premiumtable

I need output as below in a table as startdate ENddate

SQL

start_date | ENd_date
2016-01-02 | 2016-01-04
2016-01-05 | 2016-01-10  -date from premium table
2016-01-11 | 2016-01-14
2016-01-15 | 2016-01-20  -date from premium table
2016-01-21 | 2016-01-30

-----------------------------------------------------------------------
declare @startdate datetime ='2016-01-02'
declare @enddate datetime = '2016-01-30'

declare @premiumtable table 
(
startdate datetime,
enddate datetime
)
  
insert into @premiumtable values ('2016-01-05','2016-01-10')
insert into @premiumtable values ('2016-01-15','2016-01-20')

Posted 15-Jun-15 22:38pm

mahadevkarekar

Updated 15-Jun-15 22:52pm

Abhipal Singh

v2

Add a Solution

Comments

Hamassss 16-Jun-15 4:50am

You question is bit unclear. So from 2016-01-02 to 2016-01-30 you need to split in ranges from dates from premium table, and for example 2016-01-10 is date from premium table? but shoulnd't 2016-01-04 also be date from premium table ? why you split is on that date also ?

mahadevkarekar 16-Jun-15 6:22am

1 2 3 4 [5 6 7 8 9 10] 11 12 13 14 [15 16 17 18 19 20] 21 22 23 24 ...30

i need to split using the date ranges defined in @premiumtable.

Tomas Takac 16-Jun-15 7:41am

I would say a cursor is the best solution here.

mahadevkarekar 16-Jun-15 11:13am

could you please illustrate an example?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2015-06-16T12:09:00

I managed to put it into a query. First I show you the code, then I'll explain.

SQL

; with cte1 as
(
  select row_number() over(order by startdate) id, * from @premiumtable
)
, cte2 as
(
  select startdate, enddate from cte1
  
  union all

  select dateadd(day, 1, a.enddate) as startdate, dateadd(day, -1, b.startdate) as enddate
  from cte1 a inner join cte1 b on a.id+1 = b.id
  
  union all
  
  select null as startdate, dateadd(day, -1, min(startdate)) as enddate
  from @premiumtable

  union all
  
  select dateadd(day, 1, max(enddate)) as startdate, null as enddate
  from @premiumtable
) 
select 
  case when startdate < @startdate or startdate is null then @startdate else startdate end as startdate,
  case when enddate > @enddate or enddate is null then @enddate else enddate end as enddate
from cte2 
where (enddate >= @startdate or enddate is null)
and (startdate <= @enddate or startdate is null)
order by startdate

The problem is you don't have the ranges, you need to create them. Look carefully at cte2, there are four queries in the union.
1) Select existing ranges from @premiumtable.
2) Fill in the gaps in @premiumtable.
3) Add the leading open range before the first range listed in @premiertable.
4) Add the trailing open range after the last range listed in @premiertable.

Once you have this complete set of ranges you just simply filter those based on your parameters. Select all ranges that are partially or entirely within @startdate and @enddate. Of course you need to cut the outermost ranges - this is done by the CASEs.

Here is the fiddle.[^]
Enjoy.