Hi! I currently struggling with my SQL query and getting the result that I want, I have used unions to help with duplication, but I cant seem to get some fields to map correctly.

My query is pretty simple:

select clientID, employee, appt_status, date_of_service, appt_date

FROM billing_table

WHERE date(date_of_service) BETWEEN '2022-01-01' and CURRENT_DATE()

UNION

SELECT clientID, employee, appt_status, date_of_service, appt_date

FROM appt_table

This is the results that I get:

Client ID	Employee	Appt_Status	Date_of_service	Appt_date
1	HILL,PEGGY	No Show	Null	11/02/22
2	HILL,PEGGY	Null	11/04/2022	null
2	HILL,PEGGY	Scheduled	null	11/04/2022
3	HILL,PEGGY	null	11/10/2022	null
4	HILL,PEGGY	Scheduled	null	11/15/2022

This is incorrect, for example for Client ID 2 there should only be one row of data showing that the appointment status was scheduled, a date of service of 11/04/2022 and the app_date as 11/04/2022. So, if an appointment date is null, I want to pass in the date of service and vice versa to get something like this:

Desired Results

Client ID	Employee	Appt_Status	Date_of_service	Appt_date
1	HILL,PEGGY	No Show	11/02/2022	11/02/2022
2	HILL,PEGGY	Scheduled	11/04/2022	11/04/2022
3	HILL,PEGGY	Scheduled	11/10/2022	11/10/2022
4	HILL,PEGGY	Scheduled	11/15/2022	11/15/2022

Any help would be appreciated, I do know that I need to stick with using unions and I do know the rules behind union, I am just very new to using them and writing sql.