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'