with x as ( select distinct a.memberGroupChoId, a.memberGroupName, a.memberChoId, a.memberName, format( a.affiliationDate, 'MM/dd/yyyy' ) as affiliationDate, format( a.deaffiliationDate, 'MM/dd/yyyy' ) as deaffiliationDate, case when getDate() between coalesce( a.affiliationDate, datefromParts( 1966,6,1) ) and coalesce( a.deaffiliationDate, dateadd( day, 1, getDate() )) then 'true' else 'false' end as membershipActive, sg.memberGroupName as primaryMemberGroup, sg.memberGroupChoId as primaryMemberGroupChoId, case when sg.parentGroupChoId is not null then sg.parentGroupName else cast( null as varchar ) end as secondaryMemberGroup, case when sg.parentGroupChoId is not null then sg.parentGroupChoId else cast( null as integer ) end as secondaryMemberGroupChoId from tbl_DW_1fsOperatorMemberships#_tableSuffix# a with (nolock) left outer join tbl_DW_1fsSubGroups_MFR1 sg with (nolock) on sg.subGroupChoId = a.memberGroupChoId and sg.ownerId = a.ownerId and sg.ownerType = a.ownerType where a.ownerId = and a.ownerType = and ( a.memberChoId = or a.memberChoId in ( select choId from tbl_DW_1fsOperators#_tableSuffix# with (nolock) where parentChoId = ) ) and a.memberGroupChoId = and 1 = 2 ), result as ( select * from x where not exists ( select * from x as x1 where x1.memberChoId = x.memberChoId and x1.primaryMemberGroupChoId = x.memberGroupChoId ) and not exists ( select * from x as x1 where x1.memberChoId = x.memberChoId and x1.secondaryMemberGroupChoId = x.memberGroupChoId ) ) #forJSON( 'result', attributes.datastore, 'memberGroupName' )#