I managed to put it into a query. First I show you the code, then I'll explain.
; 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.