SELECT  cl.Client_ID AS PERSID,
        ctz.NameF AS SURNAME,
        ctz.NameI AS NAME,
        ctz.NameO AS SECONDNAME,
        ctz.DateBorn AS BIRTHDATE,
        ctz.Sex AS SEX,
        adr.*,
        ph.PHONE,
        ctz.SNILS AS SNILS,
        tic.EsrnCode AS DOCTYPE,
        LEFT(ctz.Card_seria, 10) AS DOCSERIA,
        LEFT(ctz.Card_number, 30) AS DOCNUMBER,
        ctz.Date_given AS DOCDATE,
        LEFT(ctz.Org_name, 100) AS DOCORG,
        NULL AS INFO
FROM    dbo.Client cl
        JOIN dbo.Citizens ctz ON ctz.Citizen_ID = cl.Client_ID
        LEFT JOIN dbo.Types_identity_cards tic ON tic.Identity_card_type_id = ctz.Identity_card_ref
        OUTER APPLY (SELECT TOP 1
                            ds.street_kladr_code AS KLADRCODE,
                            LEFT(ddt.district_type_kladr_code + '. ' + dd.district_name, 40) AS FEDBOROUGH,
                            LEFT(setl_types.settlement_type_kladr_code + '. ' + setl.settlement_name, 40) AS TOWN,
                            LEFT(dst.street_type_kladr_code + '. ' + ds.street_name, 40) AS STREET,
                            LEFT(a.House, 4) AS HOUSE,
                            LEFT(a.Corps_number, 2) AS BUILDING,
                            LEFT(a.Apartment, 5) AS FLAT,
                            NULL AS ROOM
                     FROM   dbo.Registration r
                            JOIN dbo.Address a ON a.Address_ID = r.Address_ref
                            JOIN dbo.Dict_streets ds ON ds.street_id = a.street_ref
                            JOIN dbo.Dict_street_types dst ON dst.street_type_id = ds.street_type_ref
                            JOIN dbo.Dict_settlements setl ON setl.settlement_id = a.settlement_ref
                            JOIN dbo.Dict_settlement_types setl_types ON setl_types.settlement_type_id = setl.settlement_type_ref
                            LEFT JOIN dbo.Dict_districts dd ON dd.district_id = setl.district_ref
                                                               AND dd.region_ref = setl.region_ref
                            LEFT JOIN dbo.Dict_district_types ddt ON ddt.district_type_id = dd.district_type_ref
                     WHERE  r.Citizen_ref = cl.Client_ID
                            AND r.isRegistration = 1
                            AND GETDATE() BETWEEN r.Date_begin AND ISNULL(r.Date_end, GETDATE())
                    ) AS adr
        OUTER APPLY (SELECT TOP 1
                            cp.Number AS PHONE
                     FROM   dbo.Citizens_phones cp
                     WHERE  cp.Citizen_ref = cl.Client_ID
                    ) AS ph
WHERE   cl.date_close IS NULL