These are the results form my query

Client ID	Staff	appointment status	date of service	appointment date
1	HILL,PEGGY	No Show	NULL	11/02/2022
2	HILL,PEGGY	Scheduled	NULL	11/05/2022
2	HILL,PEGGY	NULL	11/05/2022	NULL
3	HILL,PEGGY	NULL	11/10/2022	NULL
3	HILL,PEGGY	Scheduled	NULL	11/10/2022

As you can see the ClientID of 2 has a duplicate but the appointment status and date are null, I want to get rid of all the null values so the desired results would be like the table below.

Desired Results:

Client ID	Staff	Appointment Status	Date of Service	Appointment Date
1	HILL,PEGGY	No Show	11/02/2022	11/02/2022
2	HILL,PEGGY	Scheduled	11/05/2022	11/05/2022
3	HILL,PEGGY	Scheduled	11/10/2022	11/10/2022

I have tried using both UNION and UNION ALL and the results have not changed and I am still unable to successfully map the fields (see the second table)

This is my query:

select ClientID

, Staff

, Status

, COALESCE(srvDate, apptDate) AS DateOfSrv

, COALESCE(apptDate,srvDate) AS ApptDate

from (

select clientID

, staff_name

, 'status_value' status

, FORMAT_DATETIME("%Y-%m-%d",billing.date_of_service) srvDate

, 'FORMAT_DATETIME("%Y-%m-%d", appointment.appointment_date)' apptDate

from billing_table

where cast(date_of_service as date) between '2022-11-01' and current_date()

UNION ALL

select clientID

, staff_name

, status_value Status

, 'billing.date_of_service' srvDate

, FORMAT_DATETIME("%Y-%m-%d", appoinment.appointment_date) apptDate

from appointment_table

where cast (appointment_date as date) between '2022-11-01' and current_date()

order by clientID

)

Any help is GREATLY APPRECIATED