Using CTE and outer query to count

1

Good afternoon,

In the below query, I am trying to join the number of calls which have been made after a certain date to a main outer query using the "With Calls AS" subquery. The problem I was having was showing 0 for entries with no calls. I have tried to fix that but now when it runs, it repeats the same client_id and crms_number rather than showing the true client_id and crms_number for each row. I am not sure what I have done wrong or how I can fix.

Can anyone advise what I am doing wrong please?

Thank you so much in advance!

With Calls AS
(SELECT C.client_id, C.crms_number, COUNT(id) AS [Number of Calls] from dbo.ZZ_crms_communication C
left join dbo.ZZ_crms_student_tag ST ON C.client_id=ST.client_id AND C.crms_number=ST.crms_number
where c.class = 'Phone' and c.in_out = 'Out' and date_opened >= ST.last_mo_tag_date_created
group by C.client_id, C.crms_number)

select ST.client_id, ST.crms_number, last_mo_source_tag, last_mo_tag_date_created, isnull(CA.[Number of Calls],0) from dbo.ZZ_crms_student_tag ST
left join dbo.ZZ_crms_communication C ON C.client_id=ST.client_id AND C.crms_number=ST.crms_number
left join dbo.ZZ_crms_student S ON S.client_id=ST.client_id AND S.crms_number=ST.crms_number
left join dbo.ZZ_crms_student_address SA ON SA.client_id=ST.client_id AND SA.crms_number=ST.crms_number
left join Calls CA ON CA.client_id=ST.client_id AND CA.crms_number=ST.crms_number
where last_mo_source_tag LIKE '%17%' and (last_mo_source_tag LIKE '%JAN%' or last_mo_source_tag LIKE '%Sep%')
and (sa.correspondence_phone_home IS NOT NULL OR SA.correspondence_phone_mobile IS NOT NULL OR SA.correspondence_phone_work IS NOT NULL)
and s.current_heat_status <> 'Dead'

2

You might try doing
SUM(CASE WHEN id IS NULL THEN 0 ELSE 1 END)
instead of the count.

3

Thank you, I have tried this but I am still getting repeated rows where the first four fields (everything apart from the Number of Calls bit) is repeated.

4

Dunno if it is relevant but you have a LEFT OUTER JOIN

left join dbo.ZZ_crms_student_tag

and then you reference that table in the WHERE clause

and date_opened >= ST.last_mo_tag_date_created

Personally I would avoid that - ideally put that criteria in the OUTER JOIN or, if that logic is not correct, then allow for any absent rows in the OUTER JOIN

and (date_opened >= ST.last_mo_tag_date_created OR ST.client_id IS NULL)