declare @contractId1 int = ; declare @contractId2 int = ; declare @ownerId int = ; declare @ownerType varchar(3) = ; with a1 as ( select min( r.startDate ) startDate, min( r.endDate ) endDate from tbl_TPM_Requests r with (nolock) where r.ownerId = @ownerId and r.ownerType = @ownerType and r.contractId = @contractId1 ) , a2 as ( select min( r.startDate ) startDate, min( r.endDate ) endDate from tbl_TPM_Requests r with (nolock) where r.ownerId = @ownerId and r.ownerType = @ownerType and r.contractId = @contractId1 ) , a as ( select a1.startDate contractStartDate1, a1.endDate contractEndDate1, a2.startDate contractStartDate2, a2.endDate contractEndDate2 from a1 full outer join a2 on 1 = 1 ) , s1 as ( select r.startDate, r.endDate, s.skuId, s.sku, s.skuDesc from tbl_TPM_RequestSkus rs with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = rs.ownerId and r.ownerType = rs.ownerType and r.requestId = rs.requestId inner join tbl_PRD_SKus s on s.skuId = rs.skuId and s.ownerId = rs.ownerId and s.fsl_tablecode = rs.ownerType where r.ownerId = @ownerId and r.ownerType = @ownerType and r.contractId = @contractId1 ) ,s2 as ( select r.startDate, r.endDate, s.skuId, s.sku, s.skuDesc from tbl_TPM_RequestSkus rs with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = rs.ownerId and r.ownerType = rs.ownerType and r.requestId = rs.requestId inner join tbl_PRD_SKus s on s.skuId = rs.skuId and s.ownerId = rs.ownerId and s.fsl_tablecode = rs.ownerType where r.ownerId = @ownerId and r.ownerType = @ownerType and r.contractId = @contractId2 ) , skuOverlap as ( select min( s1.startDate ) startDate, min( s1.endDate ) endDate, s1.skuId, s1.sku, s1.skuDesc from s1 inner join s2 on s2.skuId = s1.skuId where not ( s1.endDate < s2.startDate or s1.startDate > s2.endDate ) group by s1.skuId, s1.sku, s1.skuDesc ) , overlap as ( select * from skuOverlap full outer join a on 1 = 1 ) #forJSON( 'overlap', attributes.dataStore, 'sku' )#