July 13, 2018 at 2:16 pm
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?
Darrell
July 13, 2018 at 2:31 pm
Darrell Woodard - Friday, July 13, 2018 2:16 PMI have the following table: EmployeeInformation
Fields ,EmployeeInformationID ,Essentia_ID ,ADP_ID ,SSN ,FirstName ,LastName ,SupervisorOrVP ,LocationNum ,Primary ,EmployeeActive ,RecordActive ,CreatedBy ,CreateDate ,ModifiedBy ,ModifiedDateEach 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?
IF NOT EXISTS (SELECT 1 FROM DBO.EmployeeInformation WHERE EmployeeInformationID = @EmployeeInformationID and Primary = 1)
BEGIN
-- Do your remediation here
END
If this is called frequently, you should ensure that there is a supporting index to cover this query.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2018 at 3:37 pm
I swear I posted this already (somewhere at least!, another SQL help site perhaps?), but I'll post it here too just in case:
SELECT EmployeeInformationID, COUNT(*) AS Primary_Count
FROM dbo.EmployeeInformation
WHERE [Primary] = 1
GROUP BY EmployeeInformationID
HAVING COUNT(*) = 0 /* OR COUNT(*) > 1 --to also find any Emps with multi Primary marked */
ORDER BY EmployeeInformationID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply