Report employee compliancy for qualifications needed for job role

Other SQL Server Topics
1

Table 1 (Qualifications_Acquired)
ID employeeID qualID jobID Expire
1 203 1 1 11/11/2019
2 204 1 2 11/12/2019
3 205 1 1 11/11/2019
4 205 2 1 11/11/2019

Table 2 (Qualifications)
qualID Qual_Title
1 First Aid
2 Company Induction
3 Working at heights
4 Face Fit
5 Confined Spaces

Table 3 (Jobroles)
jobID Job_Name
1 Manager
2 Supervisor

Table 4 (Compliance)
ID jobID qualID
1 1 1
2 1 2
3 1 3
4 2 3
5 2 4
6 2 5

I need a query that shows me if an employee has all the qualifications (and not expired) needed for his job title from the Compliance Table.

For instance:
Job_Name employeeID qualID Compliant
Manager 203 1 True
Manager 203 2 False
Manager 203 3 False
Manager 205 1 True
Manager 205 2 True
Manager 205 3 False
Supervisor 204 3 False
Supervisor 204 4 False
Supervisor 204 5 False

I've been working on this for days trying different ways but just get tangled up and nowhere. Can someone help me please?

2

All the format has been lost so I'll try again in a table later.

3

Since your date started with today 1/11/2019, I used ExpireDate < today instead of <=

Drop table If Exists #Qualifications
Drop table If Exists #QualificationsAcquired
Drop table If Exists #JobRoles
Drop table If Exists #Compliance

Create table #QualificationsAcquired
(ID int,
 employeeID	int ,
 qualID	int,
 jobID	int,
 ExpireDate date)
insert into #QualificationsAcquired values
( 1,203,1,1,'11/11/2019')
,(2,204,1,2,'11/12/2019')
,(3,205,1,1,'11/11/2019')
,(4,205,2,1,'11/11/2019')

Create table #Qualifications
(qualID	 int,
 Qual_Title varchar(30))

 insert into #Qualifications values
( 1,'First Aid')
,(2,'Company Induction')
,(3,'Working at heights')
,(4,'Face Fit')
,(5,'Confined Spaces')

Create table #JobRoles
(jobID	int,
 Job_Name varchar(20))

 insert into #JobRoles values
 (1,'Manager')
,(2,'Supervisor')

Create Table #Compliance
(ID int,
jobID int,
qualID int)

insert into #Compliance values
 (1,1,1)
,(2,1,2)
,(3,1,3)
,(4,2,3)
,(5,2,4)
,(6,2,5)

;with cte as (
select j.JobID, J.Job_Name, q.QualID, q.Qual_Title, EmployeeID
  from #Compliance c
	join #Qualifications q
		on c.QualID = q.QualID
    join #JobRoles j
		on c.JobID = j.JobID
	join (select distinct EmployeeID, JobID from #QualificationsAcquired) e
		on c.JobID = e.JobID
	 )

select c.Job_Name, c.EmployeeID, c.QualID,  
	    case when qa.EmployeeID is null then 'False' else 'True' end as Compliant
  from Cte c
	left join #QualificationsAcquired qa
		on c.EmployeeID = qa.EmployeeID
		and c.QualID = qa.QualID
		and c.JobID = qa.JobID
		and qa.ExpireDate > cast(GetDate() as date)
order by c.JobID, c.EmployeeID, c.QualID
4 
SELECT QA_emps.employeeID, C.qualID, 
    CASE WHEN oa1.ExpireDate IS NULL THEN 'False' ELSE 'True' END AS Compliant
FROM (
    SELECT DISTINCT employeeID, jobID
    FROM #QualificationsAcquired
) AS QA_emps
INNER JOIN #Compliance C ON C.jobID = QA_emps.jobID
OUTER APPLY (
    SELECT TOP (1) *
    FROM #QualificationsAcquired QA
    WHERE QA.employeeID = QA_emps.employeeID AND QA.qualID = C.qualID
    ORDER BY QA.ExpireDate DESC /*in case they qualified more than once, get the max expiredate*/
) AS oa1
ORDER BY employeeID, qualID
5

Thanks Mike01 and ScottPletcher I''ll have a go on the db now with your answers.
I really appreciate your promp responses and I'll let you know how I get on.

Phil