declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @cho_id int = ; declare @choId int = ; declare @startMonth datetime = ; declare @endMonth datetime = ; with agreements as ( select distinct contractId from tbl_DW_OperatorAgreements#_tableSuffix# a with (nolock) where a.ownerId = @ownerId and a.ownerType = @ownerType and a.cho_Id = @cho_id and exists ( select * from tbl_DW_OperatorAnalyzerClaimDetails#_tableSuffix# cd with (nolock) where cd.ownerId = a.ownerId and cd.ownerType = a.ownertype and cd.contractId = a.contractId ) ) locations as ( select ownerId, ownerType, choId, datefromParts( 1966, 6, 1 ) firstDate, datefromparts(3000, 12, 31 ) lastDate, 1 as activeLocation from tbl_DW_1fsoperators#_tableSuffix# with (nolock) where parentChoId = @cho_id and ownerId = @ownerId and ownerType= @ownerType select ownerId, ownerType, memberCHOId as choId, memberName operatorName, datefromparts( Year( affiliationDate ), month( affiliationDate ), 1 ) firstDate, dateadd( day, -1, dateadd( month, 1, datefromParts( year(coalesce( deaffiliationDate, datefromparts( 3000, 12, 31 ))), month(coalesce( deaffiliationDate, datefromparts( 3000, 12, 31 ))), 1 ) )) lastDate, case when deaffiliationDate is not null then 0 else 1 end activeLocation from tbl_DW_1fsoperatorMemberships#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType= @ownerType and memberGroupChoId = @cho_id select ownerId, ownerType, choId, datefromParts( 1966, 6, 1 ) firstDate, datefromparts(3000, 12, 31 ) lastDate, 1 as activeLocation from tbl_DW_1fsoperators#_tableSuffix# with (nolock) where choId = @cho_id and ownerId = @ownerId and ownerType= @ownerType ) , prelim as ( select contractName, claimant, format( claimDate, 'MM/dd/yyyy' ) as claimDate, claimInvoice, format( periodStart, 'MM/dd/yyyy' ) periodStart, format( periodEnd, 'MM/dd/yyyy' ) periodEnd, sum( itemQty ) itemQty, locationName, cho_id, case when l.activeLocation = 1 then 'Y' else 'N' end'Y' as validLocation , format( l.firstDate, 'MM/dd/yyyy' ) firstDate , case when coalesce( l.lastDate, '12/31/3000' ) = '12/31/3000' then '' else format( l.lastDate, 'MM/dd/yyyy' ) end as lastDate , (( datediff( month, ( select max( periodStart ) from ( values ( periodStart ), ( l.firstDate )) as maxDate(periodStart) ), periodEnd ) + 1.0 ) / ( datediff( month, periodStart, periodEnd ) + 1.0 )) as allocationRate , convert( date, null ) firstDate , convert( date, null ) lastDate , 1.0 as allocationRate , o.operatorName from tbl_DW_OperatorAnalyzerClaimDetails#_tableSuffix# a with (nolock) inner join locations l on l.ownerId = a.ownerId and l.ownerType = a.ownerType and l.choid = a.cho_id inner join tbl_DW_1fsOperators#_tableSuffix# o with (nolock) on o.choId = a.cho_id and o.ownerId = a.ownerId and o.ownerType = a.ownertype where not ( datefromParts( year( periodStart ), month( periodStart ), 1 ) > @endMonth or dateFromParts( year( periodEnd ), month( periodEnd ), 1 ) < @startMonth ) and not ( datefromParts( year( periodStart ), month( periodStart ), 1 ) > l.lastDate or dateFromParts( year( periodEnd ), month( periodEnd ), 1 ) < l.firstDate ) and a.ownerId = @ownerId and a.ownerType= @ownerType and a.contractId in ( select contractId from agreements ) and a.contractId in ( select distinct contractId from tbl_DW_OperatorAgreements#_tableSuffix# a with (nolock) where a.ownerId = @ownerId and a.ownerType = @ownerType and a.cho_Id = @choId ) group by o.operatorName, contractName, claimant, claimDate, claimInvoice, periodStart, periodEnd, locationName, cho_id , l.activeLocation, l.firstDate, l.lastDate ) select contractName, claimant, claimDate, claimInvoice, periodStart, periodEnd, locationName, cho_id, validLocation, firstDate, lastDate, operatorName, round( itemQty * allocationRate, 0 ) as itemQty from prelim order by claimDate for json auto, include_null_values