Select first row that changed

• SmackMule

  Mr or Mrs. 500

  Points: 565

  More actions

  April 4, 2017 at 6:23 pm

  #336241

  Hi, all. I have a table (500K rows) with user manager history. Basically, it's a monthly snapshot of each user's manager at the first of each month.
  Per the attached picture/example, I'm struggling to create a query that only obtains the earliest row (based on Year/Month) for each time a manager changes, represented by the highlighted rows. In this example, the query would return three rows for JDOE3. Any help appreciated.

  

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  April 4, 2017 at 6:40 pm

  #1936676

  SmackMule - Tuesday, April 4, 2017 6:23 PM

  Hi, all. I have a table (500K rows) with user manager history. Basically, it's a monthly snapshot of each user's manager at the first of each month.
  Per the attached picture/example, I'm struggling to create a query that only obtains the earliest row (based on Year/Month) for each time a manager changes, represented by the highlighted rows. In this example, the query would return three rows for JDOE3. Any help appreciated.

  

  1) Please provide create table script and inserts for sample data and your expected output to help us help you.

  2) pseudocode:

  select empid, managerid, min(massagedyearmonth)
from table
group by empid, managerid


  You could combine your date columns into a date datatype or zero-pad the month and to CCYYMM.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  April 4, 2017 at 9:38 pm

  #1936682

  Like this is one way...
  CREATE TABLE #Test(
RowID INT IDENTITY,
EmpID CHAR(5) DEFAULT 'JDOE3',
ManagerID CHAR(6),
Yr INT,
Mo TINYINT);
GO

  INSERT INTO #Test(ManagerID, Yr, Mo)
VALUES ('DBAG44', 2016, 8),
        ('DBAG44', 2016, 9),
        ('DBAG44', 2016, 10),
        ('JOFF25', 2016, 11),
        ('JOFF25', 2016, 12),
        ('JOFF25', 2017, 1),
        ('JOFF25', 2017, 2),
        ('AHOL99', 2017, 3);

  SELECT x.EmpID
        , x.ManagerID
        , MIN(CheckDate) AS CheckDate
FROM (
SELECT EmpID,
    ManagerID,
    DATEFROMPARTS(Yr,Mo,1) AS CheckDate
FROM #Test ) x
GROUP BY x.EmpID
    , x.ManagerID;

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  April 5, 2017 at 10:56 am

  #1936861

  The approach using MIN will work with the sample data, but it's not clear that it will work with more complicated data.  Specifically, depending on the needs of the OP, it may not work if an employee has one manager, changes to a different manager, and then later changes back to the original manager.  A gaps and islands approach may be needed in this more complicated scenario.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  April 5, 2017 at 12:15 pm

  #1936885

  drew.allen - Wednesday, April 5, 2017 10:56 AM

  The approach using MIN will work with the sample data, but it's not clear that it will work with more complicated data.  Specifically, depending on the needs of the OP, it may not work if an employee has one manager, changes to a different manager, and then later changes back to the original manager.  A gaps and islands approach may be needed in this more complicated scenario.

  Drew

  Great point Drew! Jeez, I need to stop trying to answer questions quickly during work breaks!

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service