How to add join on aggregate sql

1

This sql is working fine. I need to get the patient name, which is in another table.
that also has patient-ID. Not sure how to join. other table is Genpatinfo.patient_name
select patient_id
from vouchers
group by patient_id -- groups all visits together for each patient
having year(min(Billing_date )) = year(getdate()) -- checks earliest visit year is current year
and month(min(Billing_date)) = month(getdate()) -- checks earliest visit month is current month

2

WITH t
AS
(
select patient_id
from vouchers
group by patient_id -- groups all visits together for each patient
having year(min(Billing_date )) = year(getdate()) -- checks earliest visit year is current year
and month(min(Billing_date)) = month(getdate()) -- checks earliest visit month is current month
)
SELECT t.patient_id, g.patient_name
FROM t
JOIN Genpatinfo g ON t.patient_id = g.patient_id

1 Like