USP_MAPENTITY_RESEARCHGROUP_TO_CONSTTIUENT

                    CREATE procedure dbo.USP_MAPENTITY_RESEARCHGROUP_TO_CONSTTIUENT(
                        @RECORDID uniqueidentifier
                    ) as
                        set nocount on;

                        select 
                            c.ID MAPPEDRECORDID,
                            c.NAME DESCRIPTION,
                            a.ID ADDRESSID,
                            a.ADDRESSBLOCK,
                            a.CITY,
                            s.ABBREVIATION STATE,
                            a.POSTCODE,
                            COUNTRY.ABBREVIATION COUNTRY,
                            case when ac.LATITUDE is null then pc.LATITUDE else ac.LATITUDE end LATITUDE,
                            case when ac.LONGITUDE is null then pc.LONGITUDE else ac.LONGITUDE end LONGITUDE,
                            rg.ID GROUPID,
                            rg.NAME GROUPNAME
                        from dbo.RESEARCHGROUPMEMBER rgm
                        left join dbo.RESEARCHGROUP rg    on rgm.RESEARCHGROUPID = rg.ID
                        left join dbo.CONSTITUENT c    on rgm.CONSTITUENTID = c.ID
                        left join dbo.ADDRESS a    on a.CONSTITUENTID = c.ID and a.ISPRIMARY = 1
                        left join dbo.STATE s    on a.STATEID = s.ID
                        left join dbo.COUNTRY on COUNTRY.ID = a.COUNTRYID
                        left join dbo.POSTCODECOORDINATES pc on a.POSTCODE = pc.POSTCODE
                        left join dbo.ADDRESSCOORDINATES ac    on ac.ADDRESSID = a.ID
                        where rgm.RESEARCHGROUPID = @RECORDID
                        order by c.KEYNAME