none
Create grouping based on a column status RRS feed

  • Question

  • Below is a table that is used as part of an internal review process. The table shows Employees and Status. Employees are either classed as Reviewers or Reviewee’s. This is determined that if an employee’s status has a date assigned to it then he is a reviewer if not they are a reviewee.

     

    Employee Status
    John Smith 21/01/2011
    Keith Jones Not Started
    Glen Gillard Not Started
    Poly Hammer Complete
    Tony Wood Complete
    Paul Baker 21/01/2011
    Gareth Cresswell Not Started
    Keith Grimes Complete
    Chris Pamler Not Started
    Tony Wood Complete
    Keith Jones Complete
    John Baker 21/01/2011
    Jack Jones Not Started
    Peter Beck Not Started
    Poly Hammer Complete
    Lee Coward Not Started
    Chris Pamler Complete
    Peter Thompson Not Started

    What I need to do here is create another column that will display the name of the reviewer based on the reviewee.

    Let me try and explain what the data is currently showing. So lets take the first name John Smith he is a reviewer as he is assigned to a date (21/01/2011)  where as  Keith Jones is a reviewee as he is a assigned a  value that is not a date.

    So if we take the first 5 records what that is showing me is that John Smith needs a review and all the 4 people below him are the reviewers. Got it !!!!

    So what I am looking to do is work out in SQL If employee assigned to a date then all the people below them but before the next date is show are the reviewees of that person. Table two show the outcome of what I am looking for.

     

    Employee Status Reviewer
    John Smith 21/01/2011  
    Keith Jones Not Started John Smith
    Glen Gillard Not Started John Smith
    Poly Hammer Complete John Smith
    Tony Wood Complete John Smith
    Paul Baker 21/01/2011  
    Gareth Cresswell Not Started Paul Baker
    Keith Grimes Complete Paul Baker
    Chris Pamler Not Started Paul Baker
    Tony Wood Complete Paul Baker
    Keith Jones Complete Paul Baker
    John Baker 21/01/2011  
    Jack Jones Not Started Jack Jones
    Peter Beck Not Started Jack Jones
    Poly Hammer Complete Jack Jones
    Lee Coward Not Started Jack Jones
    Chris Pamler Complete Jack Jones
    Peter Thompson Not Started Jack Jones

    Thursday, February 3, 2011 12:02 AM

Answers

  • Just keep in mind that a table is not ordered, so it will be very difficult to spot all rows under specific one, if you do not have a column that we can use to sort the table the way you want to apply the logic. In other words, how can we tell SQL Server that 'Glen Gillard' is after 'John Smith'?

    declare @T table (
    employee_name varchar(50) not null UNIQUE,
    status varchar(25) not null
    );
    
    insert into @T(employee_name, status)
    VALUES
     ('John Smith', '21/01/2011'),
     ('Glen Gillard', 'Not Started'),
     ('Paul Baker', '21/01/2011');
     
    SELECT
      *
    FROM
      @T
    WHERE
      employee_name > 'John Smith';
    
    SELECT
      *
    FROM
      @T
    WHERE
      [status] > '21/01/2011'
      AND employee_name > 'John Smith';
    GO
    

    The result is not what you were expecting, right?

    You should reconsider the use of one column for multiple uses, as you are doing with column status, and try to normalize your model.

    employee
        employee_id primary key
        fname,
        lname


    review
        reviewer (foreign key referencing employee.employee_id),
        reviewee (foreign key referencing employee.employee_id),
        review_date,
        [status],
        check constraint to enforce (reviewer <> reviwee)

    -- select all reviewers
    select *
    from employee
    where employee_id in (select reviewer from review);

    -- select all reviewers for specific date
    select *
    from employee
    where employee_id in (select reviewer from review where review_date = '20110125');

    -- select all reviewee and the name of the reviewer
    select
        ER.lname + ', ' + ER.fname as reviewee,
        R.review_date,
        R.status as review_status,
        E.lname + ', ' + E.fname as reviewer,
    from
        review as R
        inner join
        empoyee as E
        on R.reviewer = E.employee_id
        inner join
        employee as ER
        on R.reviewee = ER.employee_id

    My model is not perfect by any mean, but it can give you a better idea of the path to follow.

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by KJian_ Wednesday, February 9, 2011 9:52 AM
    Thursday, February 3, 2011 2:06 AM
    Moderator