SELECT f."xID" AS companyId,
       f."Foretag" AS companyName,
       f."xCompanyGroup" AS xCompanyGroup,
       cg."Name" AS koncern,
       cs."xID" AS companyStatus,
       f."fOrt" AS city,
       f."Orgnr" AS registrationNumber,
       (SELECT count(*) FROM "Anstallda" WHERE "xFORETA" = f."xID") AS numberOfEmployees,
       fm."Namn" AS brokerName,
       fa."Namn" AS administratorName
        ,c_company."xID" AS c_companyId,
       c_company."Foretag" AS c_companyName,
       c_company."Orgnr" AS c_companyRegistrationNumber,
       c_companyBroker."xID" AS c_companyBrokerId,
       c_companyAdmin."xID" AS c_companyAdminId,
       c_companyBroker."xKontor" AS c_brokerOffice,
       c_companyAdmin."xKontor" AS c_adminOffice,
       c_companyBroker."xBrokerTeam" AS c_brokerTeam,
       c_companyAdmin."xBrokerTeam" AS c_adminTeam,
       c_companyParameterTeam."value" AS c_teamAccess,
       c_companyParameterOffice."value" AS c_officeAccess,
       c_hcc."orgnr" AS c_heartbeatChangeoverOrgnr

FROM "Foretag" f
         LEFT JOIN "CompanyStatus" cs ON f."Status2" = cs."xID"
         LEFT JOIN "CompanyGroup" cg ON f."xCompanyGroup" = cg."xID"
         LEFT JOIN "Maklare" fm ON fm."xID"=f."xMaklar"
         LEFT JOIN "Maklare" fa ON fa."xID"=f."xansvassistent"

         LEFT JOIN "Foretag" c_company ON c_company."xID" = f."xID"

         LEFT JOIN "Maklare" c_companyBroker on c_company."xMaklar" = c_companyBroker."xID"
         LEFT JOIN "Maklare" c_companyAdmin on c_company."xansvassistent" = c_companyAdmin."xID"
         LEFT JOIN "CompanyParameter" c_companyParameterTeam
                   ON c_companyParameterTeam."xCompany"=c_company."xID"
                       AND c_companyParameterTeam."xParameterType" = 12
         LEFT JOIN "CompanyParameter" c_companyParameterOffice
                   ON c_companyParameterOffice."xCompany"=c_company."xID"
                       AND c_companyParameterOffice."xParameterType" = 13
         LEFT JOIN (SELECT DISTINCT "orgnr" FROM "HeartbeatCompanyChangeover") c_hcc
                   ON c_hcc."orgnr" = c_company."Orgnr"

WHERE  f."raderas" != 'j' AND (f."Foretag" LIKE 'släp%')
