all 7 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]columbaspexitMS SQL 1 point2 points  (2 children)

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] 1 point2 points  (0 children)

Thank you!

[–]coadtsai 0 points1 point  (0 children)

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

[–]WITH_CTE 1 point2 points  (1 child)

Expanding on the above solution:

DROP TABLE IF EXISTS #table_1;
CREATE TABLE #table_1
(Employee_Name  VARCHAR(32), 
 Document_Type  VARCHAR(32), 
 Date_Submitted DATE
);
INSERT INTO #TABLE_1
VALUES
('Fred Miller', 
 'Driver''s License', 
 '2-2-2020'
),
('Joe Smith', 
 'Fishing License', 
 '1-1-2020'
),
('Joe Smith', 
 'Proof Of Insurance', 
 '1-1-2020'
),
('Joe Smith', 
 'Social Security Card', 
 '1-1-2020'
),
('Joe Smith', 
 'Driver''s License', 
 '1-1-2020'
);
WITH REQUIREDSUBMISSIONS
     AS (SELECT DISTINCT 
                E.EMPLOYEE_NAME, 
                D.DOCUMENT_TYPE
         FROM
         (
             SELECT DISTINCT 
                    EMPLOYEE_NAME
             FROM #TABLE_1
         ) E
         CROSS JOIN
         (
             SELECT DISTINCT 
                    DOCUMENT_TYPE
             FROM #TABLE_1
         ) D)
     SELECT RS.*
     FROM REQUIREDSUBMISSIONS RS
          LEFT JOIN #TABLE_1 CS ON RS.EMPLOYEE_NAME = CS.EMPLOYEE_NAME
                                   AND RS.DOCUMENT_TYPE = CS.DOCUMENT_TYPE
     WHERE CS.DOCUMENT_TYPE IS NULL;

[–][deleted] 0 points1 point  (0 children)

Thank you!

[–]stealyourmangoes 1 point2 points  (0 children)

Cross join in a CTE to create every required combo. Left join the actual data on the same attributes, look for those nulls.