SQLTeam.com | Weblogs | Forums

Validate whether at least 1 record per user is marked as primary


I have the following table: EmployeeInformation
Fields ,EmployeeInformationID ,Essentia_ID ,ADP_ID ,SSN ,FirstName ,LastName ,SupervisorOrVP ,LocationNum ,Primary ,EmployeeActive ,RecordActive ,CreatedBy ,CreateDate ,ModifiedBy ,ModifiedDate

Each employee can have only one EmployeeInformationID but can have multiple ADP_IDs. (I inherited DB).
So ADP_ID is unique but EmployeeInformationID is not. However 1 record should be marked as primary.
I need to validate that each employee record has at least one record marked as primary.
This will be done inside a frequently used program before i do some calculations and create other records in other tables.
What is a good way to do this?

SELECT EmployeeInformationID, COUNT(*) AS Primary_Count
FROM EmployeeInformation
WHERE [Primary] = 1
GROUP BY EmployeeInformationID
HAVING COUNT(*) = 0 /*OR COUNT(*) > 1--if/when you want to see if anyone has Primary marked on more than one row*/


To enforce it going forward and once the data is cleaned, you can put a filtered unique index on it