none
Need proper query advice for updating a table column to a value found in a log table

    Question

  • Hello, 

    Apologies for the terrible title!

    My question: How do I update a value in table A with the proper value from table B when the table B row has a date value that is the first date to arrive after a date in table A?  Since I can't seem to word this question clearly, I'll explain:

    We have a table of employees with their employment begin and end dates.  Since some employees return to employment, their name appears more than once in the table.  Each time, the begin and end date values should be distinct for a given employee.  We have another table that is basically an employment log: instead of updating existing rows, a new row is added.  When Employee X is hired, a row is added to the log.  If and when Employee X employment is terminated, a new row is added to the log.  Every time a "hire" or "terminate" record is added to the log table, the employee table gets updated with either a) a new row for the newly-hired employee, or b) a termination date for the existing employee record.

    Well something about the update process is buggy, because some of the employee termination dates aren't getting updated.  My task is to update the existing Employee termination dates for those employees and employments that have ended.

    Here's the T-SQL, complete with comments specifying where the employee termination date values should be updated, as well as the correct update value.  But how do I do it PROPERLY in T-SQL?  I always try to avoid cursors and enact some type of set-based method, but these types of updates throw me for a loop.  (Not enough practice, I guess.)

    Thanks,
    Eric

    USE tempdb
    GO

    CREATE TABLE Employments (
     EmployeeID int 
    , StartDate date NOT NULL
    , EndDate date DEFAULT(('9999-12-31'))
    )
    GO

    INSERT INTO Employments VALUES 
     (1, '2000-01-01', '2005-01-18') --EndDate is correct.
    , (1, '2010-06-23', '9999-12-31') --EndDate s/b updated to 2011-09-14.
    , (1, '2013-02-01', '9999-12-31') --EndDate should not change, employee is still employed.
    , (2, '2005-06-15', '2005-10-24') --EndDate is correct.
    , (2, '2007-02-19', '9999-12-31') --EndDate should not change, employee is still employed.
    , (3, '2006-09-18', '9999-12-31') --EndDate should be updated to 2007-05-05.
    GO

    CREATE TABLE EmploymentLog (
     EmployeeID int
    , UpdateDate datetime
    , UpdateCode int
    )
    GO

    INSERT INTO EmploymentLog VALUES 
     (1, '2000-01-01', 1) --1 is obviously "hired" (StartDate)
    , (1, '2005-01-18', 42) --2 is obviously "terminated" (EndDate)
    , (1, '2010-06-23', 1)
    , (1, '2011-09-14', 42)
    , (1, '2013-02-01', 1)
    , (2, '2005-06-15', 1)
    , (2, '2005-10-24', 42)
    , (2, '2007-02-19', 1)
    , (3, '2006-09-18', 1)
    , (3, '2007-05-05', 42)
    GO



    Wednesday, October 16, 2013 8:35 PM

Answers

  • Try this..

    ;With Hire
    as
    (
    SELECT *,Row_Number() Over(Partition by EmployeeID Order by UpdateDate) as rnum FROM EmploymentLog where UpdateCode=1
    ),
    Dep
    as
    (
    SELECT *,Row_Number() Over(Partition by EmployeeID Order by UpdateDate) as rnum FROM EmploymentLog where UpdateCode=42
    )
    
    Update Employments 
    Set EndDate=Dep.UpdateDate
    From Employments
    Inner Join Hire on Employments.EmployeeID=Hire.EmployeeID and Employments.StartDate=Hire.UpdateDate
    Inner Join Dep on Hire.EmployeeID=Dep.EmployeeID and Hire.rnum=Dep.rnum


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Wednesday, October 16, 2013 8:52 PM
  • CREATE TABLE Employments (
     EmployeeID int 
    , StartDate date NOT NULL
    , EndDate date DEFAULT(('9999-12-31'))
    )
    GO
    INSERT INTO Employments VALUES 
     (1, '2000-01-01', '2005-01-18')--EndDate is correct.
    , (1, '2010-06-23', '9999-12-31')--EndDate s/b updated to 2011-09-14.
    , (1, '2013-02-01', '9999-12-31')--EndDate should not change, employee is still employed.
    , (2, '2005-06-15', '2005-10-24')--EndDate is correct.
    , (2, '2007-02-19', '9999-12-31')--EndDate should not change, employee is still employed.
    , (3, '2006-09-18', '9999-12-31')--EndDate should be updated to 2007-05-05.
    GO
    CREATE TABLE EmploymentLog (
     EmployeeID int
    , UpdateDate datetime
    , UpdateCode int
    )
    GO
    INSERT INTO EmploymentLog VALUES 
     (1, '2000-01-01', 1)--1 is obviously "hired" (StartDate)
    , (1, '2005-01-18', 42) --2 is obviously "terminated" (EndDate)
    , (1, '2010-06-23', 1)
    , (1, '2011-09-14', 42)
    , (1, '2013-02-01', 1)
    , (2, '2005-06-15', 1)
    , (2, '2005-10-24', 42)
    , (2, '2007-02-19', 1)
    , (3, '2006-09-18', 1)
    , (3, '2007-05-05', 42)
    GO 
    ;With mycte as
    (select *,row_number() Over(Partition By EmployeeID  Order By UpdateDate) rn from EmploymentLog)
    ,mycte1 as
    (select *, row_number() Over(Partition By EmployeeID  Order By StartDate) rn from Employments)
    ,mycteFinal as (
    Select m.EmployeeID,m.StartDate, m.EndDate, t.UpdateDate2 from mycte1 m INNER JOIN 
    (Select m1.EmployeeID, m1.UpdateDate, m2.UpdateDate as UpdateDate2 
    from mycte m1 
    LEFT JOIN mycte m2 ON m1.rn=m2.rn-1 AND  m1.EmployeeID=m2.EmployeeID
    WHERE m1.rn%2=1) t ON m.EmployeeID=t.EmployeeID AND m.StartDate=t.UpdateDate)
    Update mycteFinal
    Set EndDate=UpdateDate2
    WHERE UpdateDate2 IS NOT NULL
    Select * from Employments
    drop table   Employments
    drop table   EmploymentLog

    Wednesday, October 16, 2013 9:06 PM
    Moderator
  • There are many ways to do this.  But I would do it like this:

    ;WITH cteLog AS
    (
    	select
    	*,RowNum = ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EmployeeID, UpdateDate)
    	from dbo.EmploymentLog
    	WHERE UpdateCode IN (1,42)
    )
    UPDATE dbo.Employments
    	SET EndDate = src.EndDate
    FROM dbo.Employments dest
    	INNER JOIN  
    	(
    	SELECT 
    		startdt.EmployeeID,
    		StartDate = startdt.UpdateDate,
    		EndDate = enddt.UpdateDate
    	FROM cteLog as startdt
    		INNER JOIN cteLog as enddt
    		ON enddt.EmployeeID = startdt.EmployeeID
    			AND startdt.RowNum + 1 = enddt.RowNum 
    			AND enddt.UpdateCode = 42
    	WHERE startdt.UpdateCode = 1
    ) src
    ON src.EmployeeID = dest.EmployeeID
    AND src.StartDate = dest.StartDate
    
    WHERE dest.EndDate <> src.EndDate 

    Wednesday, October 16, 2013 9:19 PM
    Moderator

All replies

  • Try this..

    ;With Hire
    as
    (
    SELECT *,Row_Number() Over(Partition by EmployeeID Order by UpdateDate) as rnum FROM EmploymentLog where UpdateCode=1
    ),
    Dep
    as
    (
    SELECT *,Row_Number() Over(Partition by EmployeeID Order by UpdateDate) as rnum FROM EmploymentLog where UpdateCode=42
    )
    
    Update Employments 
    Set EndDate=Dep.UpdateDate
    From Employments
    Inner Join Hire on Employments.EmployeeID=Hire.EmployeeID and Employments.StartDate=Hire.UpdateDate
    Inner Join Dep on Hire.EmployeeID=Dep.EmployeeID and Hire.rnum=Dep.rnum


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Wednesday, October 16, 2013 8:52 PM
  • CREATE TABLE Employments (
     EmployeeID int 
    , StartDate date NOT NULL
    , EndDate date DEFAULT(('9999-12-31'))
    )
    GO
    INSERT INTO Employments VALUES 
     (1, '2000-01-01', '2005-01-18')--EndDate is correct.
    , (1, '2010-06-23', '9999-12-31')--EndDate s/b updated to 2011-09-14.
    , (1, '2013-02-01', '9999-12-31')--EndDate should not change, employee is still employed.
    , (2, '2005-06-15', '2005-10-24')--EndDate is correct.
    , (2, '2007-02-19', '9999-12-31')--EndDate should not change, employee is still employed.
    , (3, '2006-09-18', '9999-12-31')--EndDate should be updated to 2007-05-05.
    GO
    CREATE TABLE EmploymentLog (
     EmployeeID int
    , UpdateDate datetime
    , UpdateCode int
    )
    GO
    INSERT INTO EmploymentLog VALUES 
     (1, '2000-01-01', 1)--1 is obviously "hired" (StartDate)
    , (1, '2005-01-18', 42) --2 is obviously "terminated" (EndDate)
    , (1, '2010-06-23', 1)
    , (1, '2011-09-14', 42)
    , (1, '2013-02-01', 1)
    , (2, '2005-06-15', 1)
    , (2, '2005-10-24', 42)
    , (2, '2007-02-19', 1)
    , (3, '2006-09-18', 1)
    , (3, '2007-05-05', 42)
    GO 
    ;With mycte as
    (select *,row_number() Over(Partition By EmployeeID  Order By UpdateDate) rn from EmploymentLog)
    ,mycte1 as
    (select *, row_number() Over(Partition By EmployeeID  Order By StartDate) rn from Employments)
    ,mycteFinal as (
    Select m.EmployeeID,m.StartDate, m.EndDate, t.UpdateDate2 from mycte1 m INNER JOIN 
    (Select m1.EmployeeID, m1.UpdateDate, m2.UpdateDate as UpdateDate2 
    from mycte m1 
    LEFT JOIN mycte m2 ON m1.rn=m2.rn-1 AND  m1.EmployeeID=m2.EmployeeID
    WHERE m1.rn%2=1) t ON m.EmployeeID=t.EmployeeID AND m.StartDate=t.UpdateDate)
    Update mycteFinal
    Set EndDate=UpdateDate2
    WHERE UpdateDate2 IS NOT NULL
    Select * from Employments
    drop table   Employments
    drop table   EmploymentLog

    Wednesday, October 16, 2013 9:06 PM
    Moderator
  • There are many ways to do this.  But I would do it like this:

    ;WITH cteLog AS
    (
    	select
    	*,RowNum = ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EmployeeID, UpdateDate)
    	from dbo.EmploymentLog
    	WHERE UpdateCode IN (1,42)
    )
    UPDATE dbo.Employments
    	SET EndDate = src.EndDate
    FROM dbo.Employments dest
    	INNER JOIN  
    	(
    	SELECT 
    		startdt.EmployeeID,
    		StartDate = startdt.UpdateDate,
    		EndDate = enddt.UpdateDate
    	FROM cteLog as startdt
    		INNER JOIN cteLog as enddt
    		ON enddt.EmployeeID = startdt.EmployeeID
    			AND startdt.RowNum + 1 = enddt.RowNum 
    			AND enddt.UpdateCode = 42
    	WHERE startdt.UpdateCode = 1
    ) src
    ON src.EmployeeID = dest.EmployeeID
    AND src.StartDate = dest.StartDate
    
    WHERE dest.EndDate <> src.EndDate 

    Wednesday, October 16, 2013 9:19 PM
    Moderator
  • Wow, it took me longer to type my question than it did to get 3 solutions!  Thanks!  They all worked perfectly.

    Very graciously,
    -Eric

    Wednesday, October 16, 2013 9:40 PM
  • To track the history of, say, Personnel we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a Employment_History had a particular value. Here is the skeleton that will help”

    CREATE TABLE Events
    (event_id CHAR(10) NOT NULL,
     previous_event_end_date DATE NOT NULL  
     CONSTRAINT Chained_Dates  
      REFERENCES Events (event_end_date), 
     event_start_date DATE NOT NULL, 
     event_end_date DATE UNIQUE, -- null means event in progress
      PRIMARY KEY (event_id, event_start_date), 
     CONSTRAINT Event_Order_Valid 
      CHECK (event_start_date <= event_end_date), 
     CONSTRAINT Chained_Dates 
      CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;
    GO

    -- insert a starter row
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');
    GO

    -- enable the constraint in the table
    ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;
    GO

    -- this works
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

    -- this fails
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15'); 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, October 16, 2013 10:47 PM