Need T-SQL help aggregating like records resulting with start date and end date

• gary.proctor

  Mr or Mrs. 500

  Points: 580

  More actions

  July 6, 2011 at 12:17 pm

  #240448

  I am working with a feed of employee data that is updated daily.

  I intend to use T-SQL to aggregate multiple identical daily records into a single record using a start and stop date to denote the date range each record is effective.

  I start with the table dbo.EmployeeDaily:

  FileDate EmployeeId EmployeeName ManagerId (ManagerName) <---Not a real column

  5/1/2011 101 Allen, Adam 105 (Jones, John)

  5/2/2011 101 Allen, Adam 105 (Jones, John)

  5/3/2011 101 Allen, Adam 105 (Jones, John)

  5/4/2011 101 Allen, Adam 105 (Jones, John)

  5/1/2011 102 Boop, Betty 106 (King, Kim)

  5/2/2011 102 Boop, Betty 106 (King, Kim)

  5/3/2011 102 Boop, Betty 107 (Lewis, Lee)

  5/4/2011 102 Boop, Betty 107 (Lewis, Lee)

  5/1/2011 103 Cane, Candy 105 (Jones, John)

  5/2/2011 103 Cane, Candy 105 (Jones, John)

  5/3/2011 103 Cane, Candy 105 (Jones, John)

  5/3/2011 104 Davis, Diane 105 (Jones, John)

  5/4/2011 104 Davis, Diane 105 (Jones, John)

  5/1/2011 105 Jones, John 108 (Miller, Mark)

  5/2/2011 105 Jones, John 108 (Miller, Mark)

  5/3/2011 105 Jones, John 108 (Miller, Mark)

  5/4/2011 105 Jones, John 108 (Miller, Mark)

  5/1/2011 106 King, Kim 108 (Miller, Mark)

  5/2/2011 106 King, Kim 108 (Miller, Mark)

  5/3/2011 106 King, Kim 108 (Miller, Mark)

  5/4/2011 106 King, Kim 108 (Miller, Mark)

  5/1/2011 107 Lewis, Lee 108 (Miller, Mark)

  5/2/2011 107 Lewis, Lee 108 (Miller, Mark)

  5/3/2011 107 Lewis, Lee 108 (Miller, Mark)

  5/1/2011 108 Miller, Mark 108 (Miller, Mark)

  5/2/2011 108 Miller, Mark 108 (Miller, Mark)

  5/3/2011 108 Miller, Mark 108 (Miller, Mark)

  5/4/2011 108 Miller, Mark 108 (Miller, Mark)

  …

  and I need the final results to populate the table dbo.EmployeeHistory:

  StartDate EndDate EmployeeId EmployeeName ManagerId

  5/1/2011 5/4/2011 101 Allen, Adam 105

  5/1/2011 5/2/2011 102 Boop, Betty 106

  5/3/2011 5/4/2011 102 Boop, Betty 107

  5/1/2011 5/3/2011 103 Cane, Candy 105

  5/3/2011 5/4/2011 104 Davis, Diane 105

  5/1/2011 5/4/2011 105 Jones, John 108

  5/1/2011 5/4/2011 106 King, Kim 108

  5/1/2011 5/4/2011 107 Lewis, Lee 108

  5/1/2011 5/4/2011 108 Miller, Mark 108

  ...

  Can someone help me with the T-SQL required to most efficiently process this transformation?

  I am trying to avoid Jeff's infamous REBAR if at all possible!

  Thanks

  -Gary

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  July 6, 2011 at 12:25 pm

  #1349223

  Have a look here

  http://www.sqlservercentral.com/articles/T-SQL/71550/

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  July 6, 2011 at 12:26 pm

  #1349224

  Totally unparsed/unchecked but...

  SELECT MIN(FileDate) as StartDate

  , MAX(FileDate) as EndDate

  , EmployeeId

  ,EmployeeName

  ,ManagerId

  from TheTable

  Where FileDate BETWEEN @beginning and @ending

  GROUP BY EmployeeID, EmployeeName, ManagerId

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  July 6, 2011 at 12:27 pm

  #1349225

  Could you do something like:

  SELECT

  MIN(FileDate) AS 'StartDate',

  MAX(FileDate) AS 'EndDate',

  EmployeeID,

  EmployeeName,

  ManagerID

  FROM dbo.EmployeeDaily

  GROUP BY EmployeeID, EmployeeName, ManagerID

  gary.proctor (7/6/2011)

  ---

  I am working with a feed of employee data that is updated daily.

  I intend to use T-SQL to aggregate multiple identical daily records into a single record using a start and stop date to denote the date range each record is effective.

  I start with the table dbo.EmployeeDaily:

  FileDate EmployeeId EmployeeName ManagerId (ManagerName) <---Not a real column

  5/1/2011 101 Allen, Adam 105 (Jones, John)

  5/2/2011 101 Allen, Adam 105 (Jones, John)

  5/3/2011 101 Allen, Adam 105 (Jones, John)

  5/4/2011 101 Allen, Adam 105 (Jones, John)

  5/1/2011 102 Boop, Betty 106 (King, Kim)

  5/2/2011 102 Boop, Betty 106 (King, Kim)

  5/3/2011 102 Boop, Betty 107 (Lewis, Lee)

  5/4/2011 102 Boop, Betty 107 (Lewis, Lee)

  5/1/2011 103 Cane, Candy 105 (Jones, John)

  5/2/2011 103 Cane, Candy 105 (Jones, John)

  5/3/2011 103 Cane, Candy 105 (Jones, John)

  5/3/2011 104 Davis, Diane 105 (Jones, John)

  5/4/2011 104 Davis, Diane 105 (Jones, John)

  5/1/2011 105 Jones, John 108 (Miller, Mark)

  5/2/2011 105 Jones, John 108 (Miller, Mark)

  5/3/2011 105 Jones, John 108 (Miller, Mark)

  5/4/2011 105 Jones, John 108 (Miller, Mark)

  5/1/2011 106 King, Kim 108 (Miller, Mark)

  5/2/2011 106 King, Kim 108 (Miller, Mark)

  5/3/2011 106 King, Kim 108 (Miller, Mark)

  5/4/2011 106 King, Kim 108 (Miller, Mark)

  5/1/2011 107 Lewis, Lee 108 (Miller, Mark)

  5/2/2011 107 Lewis, Lee 108 (Miller, Mark)

  5/3/2011 107 Lewis, Lee 108 (Miller, Mark)

  5/1/2011 108 Miller, Mark 108 (Miller, Mark)

  5/2/2011 108 Miller, Mark 108 (Miller, Mark)

  5/3/2011 108 Miller, Mark 108 (Miller, Mark)

  5/4/2011 108 Miller, Mark 108 (Miller, Mark)

  …

  and I need the final results to populate the table dbo.EmployeeHistory:

  StartDate EndDate EmployeeId EmployeeName ManagerId

  5/1/2011 5/4/2011 101 Allen, Adam 105

  5/1/2011 5/2/2011 102 Boop, Betty 106

  5/3/2011 5/4/2011 102 Boop, Betty 107

  5/1/2011 5/3/2011 103 Cane, Candy 105

  5/3/2011 5/4/2011 104 Davis, Diane 105

  5/1/2011 5/4/2011 105 Jones, John 108

  5/1/2011 5/4/2011 106 King, Kim 108

  5/1/2011 5/4/2011 107 Lewis, Lee 108

  5/1/2011 5/4/2011 108 Miller, Mark 108

  ...

  Can someone help me with the T-SQL required to most efficiently process this transformation?

  I am trying to avoid Jeff's infamous REBAR if at all possible!

  Thanks

  -Gary

• gary.proctor

  Mr or Mrs. 500

  Points: 580

  More actions

  July 6, 2011 at 12:31 pm

  #1349229

  Wow.

  Thank you for the immediate responses.

  Unfortunately I abreviated the example for the sake of space.

  While MIN & MAX solution will work nearly every time...

  It is possible that an employee may have managerA move to MangerB and then move back to ManagerA resulting in incorrect data.

  My apologies for making an assumption and not posting a better example.

  Any other ideas?

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  July 6, 2011 at 12:39 pm

  #1349232

  So how do you know which manager to display? What's the criteria around that?

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  July 6, 2011 at 1:27 pm

  #1349263

  This should give you what you want. I've added a couple of extra rows of test data.

  SET DATEFORMAT MDY

  DECLARE @EmployeeDaily TABLE(FileDate DATETIME, EmployeeId INT,EmployeeName VARCHAR(30),ManagerId INT)

  INSERT INTO @EmployeeDaily(FileDate,EmployeeId,EmployeeName,ManagerId)

  SELECT '5/1/2011' ,101, 'Allen, Adam', 105 UNION ALL

  SELECT '5/2/2011' ,101, 'Allen, Adam', 105 UNION ALL

  SELECT '5/3/2011' ,101, 'Allen, Adam', 105 UNION ALL

  SELECT '5/4/2011' ,101, 'Allen, Adam', 105 UNION ALL

  SELECT '5/1/2011' ,102, 'Boop, Betty', 106 UNION ALL

  SELECT '5/2/2011' ,102, 'Boop, Betty', 106 UNION ALL

  SELECT '5/3/2011' ,102, 'Boop, Betty', 107 UNION ALL

  SELECT '5/4/2011' ,102, 'Boop, Betty', 107 UNION ALL

  SELECT '5/5/2011' ,102, 'Boop, Betty', 106 UNION ALL --

  SELECT '5/6/2011' ,102, 'Boop, Betty', 106 UNION ALL --

  SELECT '5/1/2011' ,103, 'Cane, Candy', 105 UNION ALL

  SELECT '5/2/2011' ,103, 'Cane, Candy', 105 UNION ALL

  SELECT '5/3/2011' ,103, 'Cane, Candy', 105 UNION ALL

  SELECT '5/3/2011' ,104, 'Davis, Diane', 105 UNION ALL

  SELECT '5/4/2011' ,104, 'Davis, Diane', 105 UNION ALL

  SELECT '5/1/2011' ,105, 'Jones, John', 108 UNION ALL

  SELECT '5/2/2011' ,105, 'Jones, John', 108 UNION ALL

  SELECT '5/3/2011' ,105, 'Jones, John', 108 UNION ALL

  SELECT '5/4/2011' ,105, 'Jones, John', 108 UNION ALL

  SELECT '5/1/2011' ,106, 'King, Kim', 108 UNION ALL

  SELECT '5/2/2011' ,106, 'King, Kim', 108 UNION ALL

  SELECT '5/3/2011' ,106, 'King, Kim', 108 UNION ALL

  SELECT '5/4/2011' ,106, 'King, Kim', 108 UNION ALL

  SELECT '5/1/2011' ,107, 'Lewis, Lee', 108 UNION ALL

  SELECT '5/2/2011' ,107, 'Lewis, Lee', 108 UNION ALL

  SELECT '5/3/2011' ,107, 'Lewis, Lee', 108 UNION ALL

  SELECT '5/1/2011' ,108, 'Miller, Mark', 108 UNION ALL

  SELECT '5/2/2011' ,108, 'Miller, Mark', 108 UNION ALL

  SELECT '5/3/2011' ,108, 'Miller, Mark', 108 UNION ALL

  SELECT '5/4/2011' ,108, 'Miller, Mark', 108;

  WITH CTE AS (

  SELECT FileDate,EmployeeId,EmployeeName,ManagerId,

  ROW_NUMBER() OVER(PARTITION BY EmployeeId ORDER BY FileDate) -

  ROW_NUMBER() OVER(PARTITION BY EmployeeId,ManagerId ORDER BY FileDate) AS rnDiff

  FROM @EmployeeDaily)

  SELECT MIN(FileDate) AS StartDate,

  MAX(FileDate) AS EndDate,

  EmployeeId,EmployeeName,ManagerId

  FROM CTE

  GROUP BY EmployeeId,EmployeeName,ManagerId,rnDiff

  ORDER BY EmployeeId,StartDate;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Nevyn

  SSChampion

  Points: 14210

  More actions

  July 6, 2011 at 1:43 pm

  #1349269

  gary.proctor (7/6/2011)

  ---

  Wow.

  Thank you for the immediate responses.

  Unfortunately I abreviated the example for the sake of space.

  While MIN & MAX solution will work nearly every time...

  It is possible that an employee may have managerA move to MangerB and then move back to ManagerA resulting in incorrect data.

  My apologies for making an assumption and not posting a better example.

  Any other ideas?

  Are you saying that an employee should have separate records for each time they switch managers, or that the query should show their most recent manager and one record per employee?

• gary.proctor

  Mr or Mrs. 500

  Points: 580

  More actions

  July 6, 2011 at 2:51 pm

  #1349312

  yes a record whowing the history with date rages of who reported to who and when

• gary.proctor

  Mr or Mrs. 500

  Points: 580

  More actions

  July 7, 2011 at 6:20 am

  #1349622

  Mark-101232

  Thank you for both the reference and the example.

  This is exactly what I needed!

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  July 7, 2011 at 6:34 am

  #1349638

  gary.proctor (7/7/2011)

  ---

  Mark-101232

  Thank you for both the reference and the example.

  This is exactly what I needed!

  Thanks for the feedback.

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537