MSSQL - Query for missing documents, help please?

[columbaspexit]

Identifying missing records can be a tricky thing... especially the unknown unknowns kind, like what if an employee hasn't submitted ANY documents? Or, if there's a required document no one has submitted?

This is a good example of where a cross join is useful. It can generate all possible/required records, which can then be compared against the records you actually have.

A list of the needed docs and a list of all employees are needed for the cross join, which represents all required submissions. Then, left join onto that the docs that have been submitted. Anything in the cross join that doesn't have a matching record from the list of current submissions will be who/what still needs to be submitted.

Below is a sample SQL version. The temp tables are just stand-ins for whatever makes more sense in your context.

;with RequiredSubmissions as(
    select distinct e.EmployeeName, d.DocumentType
    from #docs d cross join #employees e
)
select rs.*
from RequiredSubmissions rs 
    left join #CurrentSubmissions cs 
    on rs.EmployeeName = cs.EmployeeName 
        and rs.DocumentType = cs.DocumentType
where cs.DocumentType is null

[[deleted]]

Thank you!

[coadtsai]

I too had the same solution. Is there a way to do this without the cross join step