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

  • gary.proctor

    Mr or Mrs. 500

    Points: 580

    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: 26760

    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

    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: 18135

    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

    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

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

  • Mark Cowne

    One Orange Chip

    Points: 26760

    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

    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

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

  • gary.proctor

    Mr or Mrs. 500

    Points: 580

    Mark-101232

    Thank you for both the reference and the example.

    This is exactly what I needed!

  • Mark Cowne

    One Orange Chip

    Points: 26760

    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

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply