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? 
    2018년 7월 13일 금요일 오후 8:19

모든 응답

  • How are going to identity that primary record ? based on that , I think, approach could be defined

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 7월 13일 금요일 오후 8:36
    Case when row_number()Over(Partition by EmployeeInformationID Order by /* ADP_ID  */ CreateDate  ) =1 then 1 else 0 end as primaryEmployeeInformationID
    ,EmployeeInformationID ,Essentia_ID ,ADP_ID ,SSN ,FirstName ,LastName ,SupervisorOrVP ,LocationNum ,Primary ,EmployeeActive ,RecordActive ,CreatedBy ,CreateDate ,ModifiedBy ,ModifiedDate
    from EmployeeInformation

    2018년 7월 13일 금요일 오후 8:38
  • >> I have the following table: EmployeeInformation [sic] <<

    if you have a table, there is the DDL for it? All you posted is a pretty useless narrative. You don’t know what a field [sic] is in SQL; it’s nothing like a column. One of the most basic rules of data modeling is not to mix data and metadata (createdby, createdate, modifiedby, modifieddate) in the same table. Look what happens to all the creation information you put into each row. When that row is deleted? Answer: your audit trail disappears. 

    By definition, a table must have a key. This is not an option!From your narrative, which is contradictory, it looks like you’re using the adp_id as a key, but then you tell us that there’s multiple keys. I would assume that ADP is the payroll service I know by that name. I’m going to guess that what you’re calling the “employee_information_id” is a bad attempt at making a single column key for this non-table. 

    We don’t write with flags in SQL; that was assembly language programming so your activity flags, etc. are simply not relational. Things that have to do with the supervisor in the employee should be another table shows the relationship between them; a supervisor is not an attribute of an employee! We can’t figure out is the first and last names you show belong to the supervisor or to the employee. 

    It would’ve been nice if you would posted some sample data, so we could painfully, meticulously try to figure out the specs that you will not tell us.  Here is the usual idiom for ordering a set of things, such as job skills (I am using the Dictionary of Occupational Title codes)

    CREATE TABLE Personnel_Skills
    (emp_id CHAR(9) NOT NULL 
       REFERENCES Personnel(emp_id)
    dot_code CHAR(3) NOT NULL,
    job_priority INTEGER NOT NULL
       CHECK(job_priority >0),
     PRIMARY KEY (emp_id, job_priority),

    >> I need to validate that each employee record [sic] has at least one record [sic] marked as primary.  <<

    SQL is based on predicates not on setting flags. We want to discover the primary skill through predicates, not assembly language coding. Let’s assume that my best job skill has the lowest priority number. I can put such a query into a view.

    (SELECT  P1.emp_id, P1.dot_code, 
                  MIN(P1.job_priority) OVER (P1.emp_id) AS primary_ job_priority
        FROM Personnel_Skills AS P1)
    SELECT emp_id, dot_code 
      FROM X
     WHERE X.job_priority =  primary_ job_priority;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2018년 7월 15일 일요일 오후 4:14
  • I need to validate that each employee record has at least one record marked as primary. 

    You can write this logic inside a procedure and raise error if the condition is not satisfied


    CREATE PROC ProcName AS IF EXISTS ( SELECT 1 FROM tablename GROUP BY EmployeeInformationID
    HAVING SUM(CASE WHEN Primary = 1 THEN 1 ELSE 0 END) = 0
    RAISERROR 'Each Employee should have a primary information set',16,1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 제안됨 Naomi NModerator 2018년 7월 15일 일요일 오후 8:54
    2018년 7월 15일 일요일 오후 5:35
  • thanks
    2018년 7월 15일 일요일 오후 6:03