update column based on max date for each employee

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  July 19, 2017 at 4:06 am

  #322767

  Kinl provide the update query for update column TeamId based on max date for each employee

  Scenario:

  An Employee can be part of any team. Once he change his Team, all the previous data entered has to be update with latest teamId.

    
  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [cdw].FactTable(
      [SID] [int] NOT NULL,
      [Employee_SID] [int] NOT NULL,
      [Team_SID] [int] NULL,
      [CreationDate] [int] NULL
  ) ON [DATA]

  GO

  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'1', 1001, 40, 20170102)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'2', 1002, 42, 20170102)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'3', 1001, 43, 20170122)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'4', 1003, 44, 20170122)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'5', 1004, 45, 20170122)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'6', 1002, 46, 20170212)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'7', 1001, 40, 20170212)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'8', 1002, 48, 20170222)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'9', 1001, 54, 20170314)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'10', 1003, 50, 20170322)
  INSERT INTO cdw.FactTable (SID, Employee_SID, Team_SID, CreationDate) VALUES  (N'11', 1003, 57, 20170407)

  select * from cdw.FactTable
   

  Expected Query

  update cdw.FactTable set Team_SID = latest sid based on creation date for each employee.

  

  

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 19, 2017 at 4:20 am

  #1951559

  Personally, this looks like, to me, that you have a many to many relationship in your table structure, These aren't ideal. Do you have access to change your database design? If so, I would recommend having an extra table and removing team ID from the table you have above. this is especially a good idea is the above is for a data warehouse (considering it's a Fact table). Instead, have a set up along the lines of the following:
  CREATE TABLE #Employee
  (SID int,
  Employee_SID int NOT NULL,
  CreationDate int); --

  CREATE TABLE #Team --Guessed the set up for this table
  (SID int,
  Team_SID int,
  CreationDate int);

  CREATE TABLE #EmployeeTeam --New composite key table
  (Employee_SID int,
  Team_SID int);
GO

  --Some quick sample data
INSERT INTO #Employee
VALUES
  (1,1001,20170102),
  (2,1002,20170102),
  (3,1003,20170102),
  (4,1001,20170107),
  (5,1003,20170110);

  INSERT INTO #Team
VALUES
  (1, 5001, 20170102),
  (2, 5002, 20170102),
  (3, 5003, 20170102);

  INSERT INTO #EmployeeTeam
VALUES
  (1001,5001),
  (1002,5002),
  (1003,5001);
GO
--Inspect the current data
SELECT E.SID, E.Employee_SID, ET.Team_SID, E.CreationDate
FROM #Employee E
  JOIN #EmployeeTeam ET ON E.Employee_SID = ET.Employee_SID;
GO
--Now you only need to do update one row when Inserting
INSERT INTO #Employee
VALUES (6,1002,20170119);
UPDATE #EmployeeTeam
SET Team_SID = 5003
WHERE Employee_SID = 1002;
GO
--Inspect the new data
SELECT E.SID, E.Employee_SID, ET.Team_SID, E.CreationDate
FROM #Employee E
  JOIN #EmployeeTeam ET ON E.Employee_SID = ET.Employee_SID;
GO
--Clean up
DROP TABLE #EmployeeTeam
DROP TABLE #Team;
DROP TABLE #Employee;
GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  July 19, 2017 at 4:33 am

  #1951562

  I donot have access to change your database design. The above table have may columns and i have used only sample columns for the context of the posting it.
  Can you provide me an query which I can apply in the existing structure

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  July 19, 2017 at 4:33 am

  #1951563

  Thom A - Wednesday, July 19, 2017 4:20 AM

  Personally, this looks like, to me, that you have a many to many relationship in your table structure, These aren't ideal. Do you have access to change your database design? If so, I would recommend having an extra table and removing team ID from the table you have above. this is especially a good idea is the above is for a data warehouse (considering it's a Fact table). Instead, have a set up along the lines of the following:
  CREATE TABLE #Employee
    (SID int,
    Employee_SID int NOT NULL,
    CreationDate int); --

  CREATE TABLE #Team --Guessed the set up for this table
    (SID int,
    Team_SID int,
    CreationDate int);

  CREATE TABLE #EmployeeTeam --New composite key table
    (Employee_SID int,
    Team_SID int);
  GO

  --Some quick sample data
  INSERT INTO #Employee
  VALUES
    (1,1001,20170102),
    (2,1002,20170102),
    (3,1003,20170102),
    (4,1001,20170107),
    (5,1003,20170110);

  INSERT INTO #Team
  VALUES
    (1, 5001, 20170102),
    (2, 5002, 20170102),
    (3, 5003, 20170102);

  INSERT INTO #EmployeeTeam
  VALUES
    (1001,5001),
    (1002,5002),
    (1003,5001);
  GO
  --Inspect the current data
  SELECT E.SID, E.Employee_SID, ET.Team_SID, E.CreationDate
  FROM #Employee E
    JOIN #EmployeeTeam ET ON E.Employee_SID = ET.Employee_SID;
  GO
  --Now you only need to do update one row when Inserting
  INSERT INTO #Employee
  VALUES (6,1002,20170119);
  UPDATE #EmployeeTeam
  SET Team_SID = 5003
  WHERE Employee_SID = 1002;
  GO
  --Inspect the new data
  SELECT E.SID, E.Employee_SID, ET.Team_SID, E.CreationDate
  FROM #Employee E
    JOIN #EmployeeTeam ET ON E.Employee_SID = ET.Employee_SID;
  GO
  --Clean up
  DROP TABLE #EmployeeTeam
  DROP TABLE #Team;
  DROP TABLE #Employee;
  GO

  I donot have access to change your database design. The above table have may columns and i have used only sample columns for the context of the posting it.
  Can you provide me an query which I can apply in the existing structure

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 19, 2017 at 4:46 am

  #1951566

  Shanmuga Raj - Wednesday, July 19, 2017 4:33 AM

  I donot have access to change your database design. The above table have may columns and i have used only sample columns for the context of the posting it.
  Can you provide me an query which I can apply in the existing structure

  I would hope you don't have access to change my database designs! That would definitely count as a security breach if you managed to do that. 😉

  If you can't change your (not my) design, then the following should work, however, I recommend bringing the many to many relationship up. It's not good design, especially in a data warehouse environment.
  UPDATE #FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
                FROM #FactTable FT
                WHERE FT.Employee_SID = #FactTable.Employee_SID
                ORDER BY FT.CreationDate DESC);


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  July 19, 2017 at 4:58 am

  #1951568

  Thom A - Wednesday, July 19, 2017 4:46 AM

  Shanmuga Raj - Wednesday, July 19, 2017 4:33 AM

  I donot have access to change your database design. The above table have may columns and i have used only sample columns for the context of the posting it.
  Can you provide me an query which I can apply in the existing structure

  I would hope you don't have access to change my database designs! That would definitely count as a security breach if you managed to do that. 😉

  If you can't change your (not my) design, then the following should work, however, I recommend bringing the many to many relationship up. It's not good design, especially in a data warehouse environment.
  UPDATE #FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
                FROM #FactTable FT
                WHERE FT.Employee_SID = #FactTable.Employee_SID
                ORDER BY CreationDate DESC);


  Hi
  I am getting all the rows updated with latest Team_Id.
  
UPDATE cdw.FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
      FROM cdw.FactTable FT
      WHERE FT.Employee_SID = FT.Employee_SID
      ORDER BY CreationDate DESC);

                  
select * from cdw.FactTable

  

  Kindly find my above query and advice

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 19, 2017 at 5:02 am

  #1951569

  Shanmuga Raj - Wednesday, July 19, 2017 4:58 AM

  Thom A - Wednesday, July 19, 2017 4:46 AM

  Shanmuga Raj - Wednesday, July 19, 2017 4:33 AM

  I donot have access to change your database design. The above table have may columns and i have used only sample columns for the context of the posting it.
  Can you provide me an query which I can apply in the existing structure

  I would hope you don't have access to change my database designs! That would definitely count as a security breach if you managed to do that. 😉

  If you can't change your (not my) design, then the following should work, however, I recommend bringing the many to many relationship up. It's not good design, especially in a data warehouse environment.
  UPDATE #FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
                FROM #FactTable FT
                WHERE FT.Employee_SID = #FactTable.Employee_SID
                ORDER BY CreationDate DESC);


  Hi
  I am getting all the rows updated with latest Team_Id.
  
UPDATE cdw.FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
      FROM cdw.FactTable FT
      WHERE FT.Employee_SID = FT.Employee_SID
      ORDER BY CreationDate DESC);

                  
select * from cdw.FactTable

  

  Kindly find my above query and advice

  Yes, it will; you changed the statement. Note the bolded sections:
  --Mine
WHERE FT.Employee_SID = #FactTable.Employee_SID
--yours
WHERE FT.Employee_SID = FT.Employee_SID
  Your WHERE clause is referencing the same field on both sides, not one from the table you are updating and the other in the subquery..

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  July 19, 2017 at 5:10 am

  #1951571

  Thom A - Wednesday, July 19, 2017 5:02 AM

  Shanmuga Raj - Wednesday, July 19, 2017 4:58 AM

  Thom A - Wednesday, July 19, 2017 4:46 AM

  Shanmuga Raj - Wednesday, July 19, 2017 4:33 AM

  I donot have access to change your database design. The above table have may columns and i have used only sample columns for the context of the posting it.
  Can you provide me an query which I can apply in the existing structure

  I would hope you don't have access to change my database designs! That would definitely count as a security breach if you managed to do that. 😉

  If you can't change your (not my) design, then the following should work, however, I recommend bringing the many to many relationship up. It's not good design, especially in a data warehouse environment.
  UPDATE #FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
                FROM #FactTable FT
                WHERE FT.Employee_SID = #FactTable.Employee_SID
                ORDER BY CreationDate DESC);


  Hi
  I am getting all the rows updated with latest Team_Id.
  
UPDATE cdw.FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
      FROM cdw.FactTable FT
      WHERE FT.Employee_SID = FT.Employee_SID
      ORDER BY CreationDate DESC);

                  
select * from cdw.FactTable

  

  Kindly find my above query and advice

  Yes, it will; you changed the statement. Note the bolded sections:
  --Mine
WHERE FT.Employee_SID = #FactTable.Employee_SID
--yours
WHERE FT.Employee_SID = FT.Employee_SID
  Your WHERE clause is referencing the same field on both sides, not one from the table you are updating and the other in the subquery..

  Hi
  When I use the sub query WHERE FT.Employee_SID = #FactTable.Employee_SID , I am getting message as below

  Msg 208, Level 16, State 0, Line 1
Invalid object name '#FactTable'.
 

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 19, 2017 at 5:20 am

  #1951572

  Your table is called FactTable. I used a temporary table in my example.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  July 19, 2017 at 5:27 am

  #1951575

  Thom A - Wednesday, July 19, 2017 5:20 AM

  Your table is called FactTable. I used a temporary table in my example.

  I am getting below message..
  can you help me with exact query
  my table is cdw.FactTable

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 19, 2017 at 5:46 am

  #1951579

  Shanmuga Raj - Wednesday, July 19, 2017 5:27 AM

  can you help me with exact query
  my table is cdw.FactTable
   

  Rather than giving you the exact query, it's important for you to understand the SQL that I've provided. You can learn from it then. It seems that, at current, you don't really understand what the query is doing, so I've rewritten it below with some annotations.
  --The table you want to update. I have used #FactTable, as I create a temporary table, rather creating both your table and schema on a database here. your table is called FactTable.
UPDATE #FactTable
--The first half of the next line states which column you want to update, in this case, Team_SID
--The second half is the start of a subquery. The TOP 1 limits the number of rows, and FT.Team_SID is selecting the value of Team_SID from the sub query #FactTable (which is aliased FT)
SET Team_SID = (SELECT TOP 1 FT.Team_SID
                --Your FROM clause, which aliases the table. This is important as you can now reference the sub query table as FT, and your outside table as #FactTable (remember, your table is NOT called #FactTable)
                FROM #FactTable FT
                -- Here it effectively joins the two tables. FT is the subquery, where as #FactTable (remember, your table is NOT called #FactTable) is the table on the OUTSIDE of the subquery, which is the table you are updating
                WHERE FT.Employee_SID = #FactTable.Employee_SID
                --The ORDER BY is important here, as you are limited the number of results to 1 (using TOP). Thus it needs an order, so you can guarantee which value you get back. Descending by CreationDate ensures the most recent record is returned.
                ORDER BY CreationDate DESC);

  Note, your table isn't called 'cdw.FactTable', it's called 'FactTable'. The schema that the table belongs to is called 'cdw'.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  July 19, 2017 at 5:51 am

  #1951581

  Shanmuga Raj - Wednesday, July 19, 2017 5:27 AM

  Thom A - Wednesday, July 19, 2017 5:20 AM

  Your table is called FactTable. I used a temporary table in my example.

  I am getting below message..
  can you help me with exact query
  my table is cdw.FactTable

  Thanks

  UPDATE cdw.FactTable
SET Team_SID = (SELECT TOP 1 FT.Team_SID
      FROM cdw.FactTable FT
      WHERE FT.Employee_SID = cdw.FactTable.Employee_SID
      ORDER BY CreationDate DESC);

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 19, 2017 at 5:54 am

  #1951582

  Piling on 😉
  😎

  ;WITH MAX_SET AS
(
SELECT
    FT.Employee_SID
 ,MAX(FT.Team_SID) AS MX_TSID
FROM dbo.FactTable    FT
GROUP BY FT.Employee_SID
)
UPDATE FT
    SET FT.Team_SID = MS.MX_TSID
FROM    dbo.FactTable    FT
INNER JOIN MAX_SET        MS
ON            FT.Employee_SID    =    MS.Employee_SID;

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 19, 2017 at 5:56 am

  #1951584

  Eirikur Eiriksson - Wednesday, July 19, 2017 5:54 AM

  Piling on 😉
  😎

  ;WITH MAX_SET AS
(
SELECT
    FT.Employee_SID
 ,MAX(FT.Team_SID) AS MX_TSID
FROM dbo.FactTable    FT
GROUP BY FT.Employee_SID
)
UPDATE FT
    SET FT.Team_SID = MS.MX_TSID
FROM    dbo.FactTable    FT
INNER JOIN MAX_SET        MS
ON            FT.Employee_SID    =    MS.Employee_SID;

  Now you're being cruel Eirikur. 😛

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 19, 2017 at 6:05 am

  #1951588

  Thom A - Wednesday, July 19, 2017 5:56 AM

  Eirikur Eiriksson - Wednesday, July 19, 2017 5:54 AM

  Piling on 😉
  😎

  ;WITH MAX_SET AS
(
SELECT
    FT.Employee_SID
 ,MAX(FT.Team_SID) AS MX_TSID
FROM dbo.FactTable    FT
GROUP BY FT.Employee_SID
)
UPDATE FT
    SET FT.Team_SID = MS.MX_TSID
FROM    dbo.FactTable    FT
INNER JOIN MAX_SET        MS
ON            FT.Employee_SID    =    MS.Employee_SID;

  Now you're being cruel Eirikur. 😛

  <smirk>
  😎