Active appointments based on active patients

Hi there, 

I have two tables, one table of patient appointments, and one table of patient Episodes, when a patient comes into care they are in an episode of care, and will have appointmets within this, So an example would be the below

appointments;

episode

thanks to help on this forum I've managed to calculate in the appointmnet table the number of unconfirmed appointments 

at any given time, the problem I have now is that I want to include whether the patient was also in an active episode at the time...

so for example, if i was to look at 02/03/2020 patients 1 3 and 4 had not had a confirmed appointment, however patient 3 was not in an active episode, so the result from the calculation should only show as 2...

Hope this makes sense and any help would be appreciated, please not I can include the episode_ID in the appointments table if needed, it's just that some staff don't add the episode correctly

Many thanks