SQL Server - Split a date range by a given date

Question

I have a table that stores an Id and an effective period indicating when it is active

PortfolioId StartDate                 EndDate
1           2018-01-01 00:00:00.000   2018-05-31 00:00:00.000
2           2017-01-01 00:00:00.000   2018-05-31 00:00:00.000

I have another table that stores a component related to the above Id and that too has an effective period. Table 2 can have more that on entry for any given entry in table 1.

PortfolioComponentId    PortfolioId SplitDate
1                       1           2018-02-28 00:00:00.000
2                       1           2018-03-31 00:00:00.000

3                       2           2017-03-31 00:00:00.000
4                       2           2017-09-20 00:00:00.000
5                       2           2018-01-15 00:00:00.000

I have a period where I am running the query for i.e

StartDate : 30-JUN-2017
End date  : 15-MAY-2018

I have looking for a result like the below, where data in table 1 is split based on the data from table 2

PortfolioId StartDate                 EndDate
1           2018-01-01 00:00:00.000   2018-02-28 00:00:00.000
1           2018-03-01 00:00:00.000   2018-03-31 00:00:00.000 - Starts from End date + 1 from the prev row
1           2018-04-01 00:00:00.000   2018-05-15 00:00:00.000

2           2017-06-30 00:00:00.000   2017-09-20 00:00:00.000 - Starts from Seach date [Portfolio component Id 3 ignored as it falls outside of search date range]
2           2017-09-21 00:00:00.000   2018-01-15 00:00:00.000
2           2018-01-16 00:00:00.000   2018-05-15 00:00:00.000 - Ends by seach end date

Data setup - In case it helps

DECLARE @SearchStartDate DATETIME = '30-JUN-2017'
DECLARE @SearchEndDate DATETIME = '15-MAY-2018'

DECLARE @Portfolio TABLE
(
    PortfolioId INT PRIMARY KEY IDENTITY(1,1),
    StartDate DATETIME,
    EndDate DATETIME
)

INSERT INTO @Portfolio
SELECT '01-JAN-2018',  '31-MAY-2018'
INSERT INTO @Portfolio
SELECT '01-JAN-2017',  '31-MAY-2018'


DECLARE @PortfolioComponents TABLE
(
    PortfolioComponentId INT PRIMARY KEY IDENTITY(1,1),
    PortfolioId INT, 
    SplitDate DATETIME
)
INSERT INTO @PortfolioComponents
SELECT 1, '28-FEB-2018'
INSERT INTO @PortfolioComponents
SELECT 1, '31-MAR-2018'

INSERT INTO @PortfolioComponents
SELECT 2, '31-MAR-2017'
INSERT INTO @PortfolioComponents
SELECT 2, '20-SEP-2017'
INSERT INTO @PortfolioComponents
SELECT 2, '15-JAN-2018'


SELECT * from @Portfolio
SELECT * from @PortfolioComponents

Answer 1

I believe I have got a result close to what I want, though not

the most ideal approach

the best approach from a performance perspective

DECLARE @Temp TABLE (BenchmarkDate  Datetime, ComponentType int, PortfolioId INT)


INSERT INTO @Temp
SELECT StartDate , 1, PortfolioId FROM @Portfolio
UNION 
SELECT EndDate , 2, PortfolioId FROM @Portfolio

INSERT INTO @Temp
SELECT SplitDate , 2, PortfolioId FROM @PortfolioComponents
UNION 
SELECT SplitDate + 1 , 1, PortfolioId FROM @PortfolioComponents

DECLARE @Results TABLE
(
    Id INT IDENTITY(1,1),
    StartDate DATETIME,
    EndDate DATETIME,
    PortfolioId INT
)

INSERT INTO @Results
SELECT  rset1.BenchmarkDate [Startdate],
        ( SELECT MIN(rset2.BenchmarkDate)
          FROM @Temp rset2
          WHERE rset2.ComponentType = 2
          AND rset2.BenchmarkDate > rset1.BenchmarkDate
          AND rset1.PortfolioId = rset2.PortfolioId) [Enddate],
          rset1.PortfolioId
FROM @Temp rset1

WHERE rset1.ComponentType = 1
ORDER BY rset1.PortfolioId, rset1.BenchmarkDate

SELECT 
    CASE WHEN (@SearchStartDate BETWEEN StartDate AND EndDate ) THEN @SearchStartDate ELSE StartDate END StartDate, 
    CASE WHEN (@SearchEndDate BETWEEN StartDate AND EndDate)  THEN @SearchEndDate ELSE EndDate END EndDate, 
    PortfolioId ,
    (
        SELECT PortfolioComponentId 
            FROM @PortfolioComponents pc 
            WHERE 
            (pc.PortfolioID = r.PortfolioId AND
            (DATEADD(d, 1, pc.SplitDate) = r.StartDate ))
    ) PortfolioComponentId
FROM @Results r
WHERE 
(@SearchStartDate < StartDate AND @SearchStartDate < EndDate AND @SearchEndDate > EndDate) 
OR 
(@SearchEndDate BETWEEN StartDate AND EndDate) 
OR 
(@SearchStartDate BETWEEN StartDate AND EndDate )