Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Find date that someone changed to their *current* job title

    One employee can have many career entries.
    Career entries contain information about job and/or salary changes.

    I need to find out the date that someone changed to their current job title

    Here's some code to hopefully clear up the problem...
    Code:
    /*
      AIM: Return date when someone got their CURRENT job title
           If no change return employees' start date.
    */
    
    IF object_id('dbo.top_career') IS NOT NULL BEGIN
      DROP VIEW dbo.top_career
    END
    
    IF object_id('dbo.career') IS NOT NULL BEGIN
      DROP TABLE dbo.career
    END
    
    IF object_id('dbo.employee') IS NOT NULL BEGIN
      DROP TABLE dbo.employee
    END
    
    CREATE TABLE dbo.employee (
       forename          varchar(10)
     , start_date        datetime
     , unique_identifier int PRIMARY KEY
    )
    
    CREATE TABLE dbo.career (
       career_date       datetime
     , job_title         varchar(30)
     , unique_identifier int --identity(1,1) PRIMARY KEY
     , parent_identifier int NOT NULL      FOREIGN KEY references dbo.employee(unique_identifier)
    )
    GO
    
    CREATE view dbo.top_career
      AS
    SELECT c.career_date
         , c.job_title
         , c.parent_identifier
         , c.unique_identifier
    FROM   dbo.career c
     INNER
      JOIN (
            SELECT Max(career_date)       As [top_career_date]
                 , Max(unique_identifier) As [top_unique_id]
                 , parent_identifier
            FROM   dbo.career
            WHERE  career_date <= GetDate()
            GROUP
                BY parent_identifier
           ) As [x]
        ON c.parent_identifier = x.parent_identifier
       AND c.career_date       = x.top_career_date
       AND c.unique_identifier = x.top_unique_id
    GO
    
    INSERT INTO dbo.employee (forename, start_date, unique_identifier)
          SELECT 'George' , '20060101', 1
    UNION SELECT 'Terry'  , '20070101', 2
    UNION SELECT 'Mark'   , '20070101', 3
    UNION SELECT 'Richard', '20080601', 4
    UNION SELECT 'Steve'  , '20080101', 5
    UNION SELECT 'Kerri'  , '20080101', 6
    
    INSERT INTO dbo.career (career_date, job_title, unique_identifier, parent_identifier)
          SELECT '20060101', 'Trainee Developer'       ,  1, 1 --\
    UNION SELECT '20070101', 'Trainee Developer'       ,  2, 1    -- Job title change on 01/01/2008
    UNION SELECT '20080101', 'Systems Developer'       ,  3, 1  --/
    UNION SELECT '20080601', 'Systems Developer'       ,  4, 1 --/
    UNION SELECT '20070101', 'Developer'               ,  5, 2 --\
    UNION SELECT '20070601', 'Developer'               ,  6, 2    -- Job title change on 01/01/2008
    UNION SELECT '20080101', 'Senior Developer'        ,  7, 2 --/
    UNION SELECT '20070101', 'Systems Architect'       ,  8, 3 --\
    UNION SELECT '20080101', 'Systems Architect'       ,  9, 3    -- Two career entries on same date with different titles.
    UNION SELECT '20080101', 'Senior Systems Architect', 10, 3 --/
    UNION SELECT '20080101', 'Developer'               , 11, 5 ----- Never changed job title, therefore first career date (or start date?)
    UNION SELECT '20080601', 'Developer'               , 12, 5 --/
    UNION SELECT '20080101', 'Trainee Analysy'         , 13, 6 --\
    UNION SELECT '20080601', 'Systems Analyst'         , 14, 6    -- Future dated career entry! Return 01/06/2008
    UNION SELECT '20090101', 'Senior Systems Analyst'  , 15, 6 --/
    
                                                           ----- Note that there is no entry for Richard! In this scenario we need to return the employee start_date
    /* Desired results
    |----------------------------+-----------------------+
    | employee.unique_identifier | last_job_title_change |
    |----------------------------+-----------------------+
    |           1                |      01/01/2008       |
    |           2                |      01/01/2008       |
    |           3                |      01/01/2008       |
    |           4                |      01/06/2008       |
    |           5                |      01/01/2008       |
    |           6                |      01/06/2008       |
    |----------------------------+-----------------------+
    */
    
    --SELECT * FROM dbo.employee
    --SELECT * FROM dbo.career
    --SELECT * FROM dbo.top_career*/
    Let me know if I've missed out any information you need

    Thanks and sorry for such a rubbish post!
    Last edited by gvee; 06-20-08 at 10:44.
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ugh, spotted my first mistake... top_career view amended in the above code.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't get your table structure. Why is your career table a hierarchy?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And another mistake - have made a further amendment to the above.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by blindman
    I don't get your table structure. Why is your career table a hierarchy?
    How is it a heirachy exactly?

    If you look at the records for employee 1
    Code:
    career_date                                            job_title                      unique_identifier parent_identifier 
    ------------------------------------------------------ ------------------------------ ----------------- ----------------- 
    2006-01-01 00:00:00.000                                Trainee Developer              1                 1
    2007-01-01 00:00:00.000                                Trainee Developer              2                 1
    2008-01-01 00:00:00.000                                Systems Developer              3                 1
    2008-06-01 00:00:00.000                                Systems Developer              4                 1
    
    (4 row(s) affected)
    This shows that they started on 01/01/2006 as a trainee developer and were promoted to systems developer on the 01/01/2008
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This almost looks worse than our old outsourced HR data feeds.
    Code:
    select e.unique_identifier, max(isnull (a.career_date, e.start_date))
    from (select min(career_date) as career_date, parent_identifier
    	from career
    	where career_date < getdate()
    	group by parent_identifier, job_title) a right outer join
    	employee e on a.parent_identifier = e.unique_identifier 
    group by all e.unique_identifier
    order by 1
    I do not see employee 2 as having a career change when you say he did. Perhaps management was lying to him about that "promotion"?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think I might have cracked it
    Code:
    SELECT p.unique_identifier
         , Coalesce(x.last_job_title_change, p.start_date) [last_job_title_change]
    FROM   dbo.employee p
     LEFT
      JOIN (
            SELECT Min(c.career_date) As [last_job_title_change]
                 , c.parent_identifier
            FROM   dbo.career c
             INNER
              JOIN dbo.top_career topc
                ON c.parent_identifier = topc.parent_identifier
               AND c.job_title = topc.job_title
            GROUP
                BY c.parent_identifier
           ) As [x]
        ON p.unique_identifier = x.parent_identifier
    But I'm not confident just yet

    ...I've confused myself with this problem
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cheers MCrowley, your solution is far more efficient than mine (I was a sucker for the red-herring top_career view!)
    Code:
    SELECT e.unique_identifier
         , Max(Coalesce(a.career_date, e.start_date)) As [last_job_title_change]
    FROM   dbo.employee e
     LEFT
      JOIN (
            SELECT Min(career_date) As [career_date]
                 , parent_identifier
            FROM   dbo.career
            WHERE  career_date < GetDate()
            GROUP
                BY parent_identifier
                 , job_title
           ) As [a]
        ON e.unique_identifier = a.parent_identifier
    GROUP
        BY e.unique_identifier
    I've re-written using a LEFT join; silly question but what is the GROUP BY ALL about?
    Last edited by gvee; 06-20-08 at 11:01.
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I have to say, the use of Min() and Max() in that query is superb - it took me a long time staring to really appreciate how it was working - fantastique!
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The group by all was an attempt at using a little known SQL Server oddity realated to outer joins. It failed, I think. If you get the same results, it can be omitted.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I was getting confused because you used "unique_identifier" in both tables, and I assumed it was a foreign key.

    Don't EVER name another column "unique_identifier", unless also plan on naming your dog "dog", or your son "Boy".

    Don't use the same column name to refer to different things in different tables either!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Blindman, you really think I came up with this design?
    Sadly it's a 3rd party product which dynamically builds it's own table and uses nothing but surrogate keys

    Good to point out though for future readers, cheers
    George
    Home | Blog

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by georgev
    ...and uses nothing but surrogate keys
    Well, at least they did SOMETHING right!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •