Search Wiki:

Find the Missing Parts of a List of Requirements

Arnie Rowland, April 25, 2008

Often it is necessary to compare a set of data against a list and find what list items are missing. It could be checking a list of materials for an assembly, verifying that a series of steps has been completed, or as in this example, verification that a set of requirements has been met by each employee.

For this scenario, one table contains the list of Employees, another table contains the RequirementsSatisfied (list items) as they are logged and recorded for each Employee. A third table contains the list of ALL Requirements .

The desired outcome is a checklist of the Requirements that each individual Employee must still satisfy. In this scenario, it is not necessary that the items in the list be completed in any specific order, only that there is a method to determine which Requirements are still unsatisfied.

Create Sample Data
-- Suppress data loading messages
SET NOCOUNT ON
 
DECLARE @Employees table
   (  RowID     int  IDENTITY,
      PersonID  int,
      LastName  varchar(20),
      FirstName varchar(15)
   )
 
INSERT INTO @Employees VALUES ( 6349, 'Smith', 'Bill' )
INSERT INTO @Employees VALUES ( 6345, 'Jones', 'Jane' )
INSERT INTO @Employees VALUES ( 6301, 'Williams', 'Robert' )
 
DECLARE @RequirementsSatisfied table
   (  RowID        int   IDENTITY, 
      PersonID     int,
      EventCodeID  int
   )
 
INSERT INTO @RequirementsSatisfied VALUES ( 6349, 13 )
INSERT INTO @RequirementsSatisfied VALUES ( 6349, 31 )
INSERT INTO @RequirementsSatisfied VALUES ( 6349, 30 )
INSERT INTO @RequirementsSatisfied VALUES ( 6349, 75 )
INSERT INTO @RequirementsSatisfied VALUES ( 6349, 74 )
INSERT INTO @RequirementsSatisfied VALUES ( 6345, 75 )
INSERT INTO @RequirementsSatisfied VALUES ( 6349, 4 )
INSERT INTO @RequirementsSatisfied VALUES ( 6345, 13 )
 
DECLARE @Requirements table
   (  RowID        int   IDENTITY, 
      EventCodeID  int,
      EventCode    varchar(25)
   )
 
INSERT INTO @Requirements VALUES ( 13, 'Criminal History' )
INSERT INTO @Requirements VALUES ( 31, 'DL' )
INSERT INTO @Requirements VALUES ( 30, 'CDL' )
INSERT INTO @Requirements VALUES ( 75, 'Employer Referral' )
INSERT INTO @Requirements VALUES ( 74, 'Drivers License' )
INSERT INTO @Requirements VALUES ( 2, 'CDA' )
INSERT INTO @Requirements VALUES ( 4, 'Employee Suggestions' )
Return to Top

  1. To solve this problem, the first step is to CROSS JOIN the Employees against the Requirements list in order to create a interim listing of all Requirements for all Employees.
  2. Then that interim listing (or resultset) is joined (using a LEFT JOIN) to the table of RequirementsSatsified in order to incorporate the Requirements that each Employee has previously completed.
  3. Finally, that resultset is filtered to remove the completed requirements. (As a result of the LEFT JOIN, the RequirementsSatisfied.PersonID column will be NULL for any Employee that still has not satisfied that Requirement.

SQL Server 2000 / SQL Server 2005 / SQL Server 2008 Solution
SELECT DISTINCT
   e.PersonID,
   e.LastName,
   e.FirstName,
   r.EventCodeID,
   r.EventCode
FROM @Employees e
   CROSS JOIN @Requirements r
   LEFT JOIN @RequirementsSatisfied rs
      ON (   rs.PersonID    = e.PersonID 
         AND rs.EventCodeID = r.EventCodeID
         )
WHERE rs.PersonID IS NULL
ORDER BY 
   e.PersonID,
   r.EventCodeID
Return to Top

The Results is a list that indicates
   Smith has one (1) outstanding Requirement to complete
   Jones has five (5) Requirements to complete
   Williams still has all seven (7) Requirements

PersonID LastName FirstName EventCodeID EventCode
6301 Williams Robert 2 CDA
6301 Williams Robert 4 Employee Suggestions
6301 Williams Robert 13 Criminal History
6301 Williams Robert 30 CDL
6301 Williams Robert 31 DL
6301 Williams Robert 74 Drivers License
6301 Williams Robert 75 Employer Referral
6345 Jones Jane 2 CDA
6345 Jones Jane 4 Employee Suggestions
6345 Jones Jane 30 CDL
6345 Jones Jane 31 DL
6345 Jones Jane 74 Drivers License
6349 Smith Bill 2 CDA
Return to Top

Performance Note
This process will require Table or Index scans, and for large datasets, may be quite intensive in demands on server resources.
___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Apr 25, 2008
Last edited Apr 27 2008 at 8:18 PM  by ArnieRowland, version 8
Updating...
Page view tracker