with agreements as ( select distinct contractId from tbl_DW_OperatorAgreements#_tableSuffix# a with (nolock) where a.ownerId = and a.ownerType = and a.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 = and ownerId = and 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 = and ownerType= and memberGroupChoId = 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 = and ownerId = and ownerType= union select #attributes.ownerId# as ownerId, '#attributes.ownerType#' as ownerType, 0 as cho_id ---> ) , 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 ) > or dateFromParts( year( periodEnd ), month( periodEnd ), 1 ) < ) and not ( datefromParts( year( periodStart ), month( periodStart ), 1 ) > l.lastDate or dateFromParts( year( periodEnd ), month( periodEnd ), 1 ) < l.firstDate ) and a.ownerId = and a.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 = and a.ownerType = and a.cho_Id = ) 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