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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply