test sql

WITH cte_employees AS (SELECT dbo."Employees"."Id" as id,
dbo."Employees"."PartyId" as party_id,
dbo."Parties"."LegalEntityId" as legal_entity_id,
CONCAT_WS(' ', dbo."Parties"."LastName", dbo."Parties"."FirstName",
dbo."Parties"."SecondName") AS full_name
FROM "dbo"."Employees"
INNER JOIN "dbo"."Parties" ON "dbo"."Employees"."PartyId" = "dbo"."Parties"."Id"
WHERE dbo."Parties"."LegalEntityId" = 8657
AND "dbo"."Employees"."EmployeeStatusRefValue" = 'APPROVED'),
cte_declarations AS (SELECT declarations.id,
declarations.employee_id,
declarations.personality_id,
user_patients.id as patient_id,
cte_employees.legal_entity_id,
declaration_statuses.code AS status_code,
declarations.start_date,
personalities.email,
CONCAT_WS(' ', personalities.last_name, personalities.first_name,
personalities.second_name) AS patient_full_name
FROM declarations
INNER JOIN user_patients
ON user_patients.personality_id = declarations.personality_id
INNER JOIN personalities ON personalities.id = declarations.personality_id
INNER JOIN cte_employees ON cte_employees.id = declarations.employee_id
INNER JOIN declaration_statuses
ON declaration_statuses.id = declarations.declaration_status_id
WHERE cte_employees.legal_entity_id = 8657
AND declaration_statuses.code = 'ACTIVE'
AND declarations.employee_id IS NOT NULL
AND declarations.division_id IS NOT NULL),
cte_reception_counters AS (SELECT receptions.legal_entity_id,
receptions.patient_id,
COUNT(receptions.patient_id) as counter
FROM receptions
WHERE receptions.legal_entity_id = 8657
AND receptions.asserted_date >= '2024-08-30'::DATE
-- AND update_date < '2024-09-30'::DATE
AND patient_id IS NOT NULL
AND employee_id IS NOT NULL
AND legal_entity_id IS NOT NULL
GROUP BY receptions.legal_entity_id, receptions.patient_id)

SELECT
-- COUNT(cte_declarations.id)
cte_declarations.id,
cte_declarations.legal_entity_id,
cte_employees.full_name,
cte_declarations.employee_id,
cte_declarations.personality_id,
cte_declarations.patient_full_name,
cte_reception_counters.counter,
contacts.phone_number,
cte_declarations.email
FROM cte_declarations
JOIN mv_stat_lvl_0_patient_contacts contacts on cte_declarations.personality_id = contacts.id
INNER JOIN cte_employees on cte_declarations.employee_id = cte_employees.id
LEFT OUTER JOIN cte_reception_counters ON cte_declarations.patient_id = cte_reception_counters.patient_id

WHERE cte_reception_counters.counter IS NULL;