WITH SubPortfolioCte AS
(
    SELECT PortfolioCode,
        EffectiveFrom,
        LEAD(EffectiveFrom) OVER (ORDER BY EffectiveFrom) AS EffectiveTo
    FROM [dbo].[tblPortfolios]
    WHERE [TimeSeriesCode] = 752
),
PortfolioProductsCte AS
(
    SELECT p.EffectiveFrom, p.EffectiveTo, pc.*
    FROM SubPortfolioCte p
    LEFT JOIN [dbo].[tblPortfolioCombinations] pc on pc.PortfolioCode = p.PortfolioCode AND pc.Proportion > 0
),
ProductDateCte AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY pp.PortfolioCode, fgd.ProductCode ORDER BY MonthEnded) MonthOrder,
        pp.PortfolioCode,
        pp.EffectiveFrom,
        pp.EffectiveTo,
        fgd.*
    from PortfolioProductsCte pp
    LEFT JOIN [dbo].[vwsFundGrowthData] fgd on fgd.ProductCode = CAST(pp.ProductCode AS nvarchar) AND pp.Proportion > 0 AND fgd.ProductType <> 0
),
ProductDateByIslandCte AS
(
    SELECT PortfolioCode,
        ProductCode,
        MIN(MonthEnded) AS RangeStart,
        MAX(MonthEnded) AS RangeEnd
    FROM ProductDateCte
    WHERE MonthEnded >= EffectiveFrom AND (MonthEnded <= EffectiveTo OR EffectiveTo IS NULL)
    GROUP BY PortfolioCode,
        ProductCode,
        DATEDIFF(month, DATEADD(month, MonthOrder, '1900-01-01'), MonthEnded)
),
ProductDateRangeCte AS
(
    SELECT PortfolioCode,
        ProductCode,
        MAX(RangeStart) AS RangeStart,
        MAX(RangeEnd) AS RangeEnd
    FROM ProductDateByIslandCte
    WHERE RangeStart <> RangeEnd
    GROUP BY PortfolioCode,
        ProductCode
),
PortfolioDateRangeCte AS
(
    SELECT PortfolioCode,
        MAX(RangeStart) AS StartDate,
        MIN(RangeEnd) AS EndDate
    FROM ProductDateRangeCte
    GROUP BY PortfolioCode
),
PortfolioDateRangeIslandCte AS
(
    SELECT *,
        SUM(CASE WHEN Islands.PreviousEndDate >= StartDate THEN 0 ELSE 1 END) OVER (ORDER BY Islands.RN) AS IslandId
    FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY StartDate, EndDate) AS RN,
            StartDate,
            EndDate,
            LAG(EndDate,1) OVER (ORDER BY StartDate, EndDate) AS PreviousEndDate
        FROM PortfolioDateRangeCte
    ) AS Islands
)
SELECT TOP(1)
    MIN(StartDate) AS IslandStartDate,
    MAX(EndDate) AS IslandEndDate
FROM PortfolioDateRangeIslandCte
GROUP BY IslandId
ORDER BY IslandEndDate DESC