update column based on max date for each employee

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 19, 2017 at 7:51 am

  #1951631

  Eirikur Eiriksson - Wednesday, July 19, 2017 6:05 AM

  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>
  😎

  ;WITH OldAndNewValues AS (

  SELECT Team_SID, NewTeam_SID = MAX(Team_SID) OVER(PARTITION BY Employee_SID)

  FROM dbo.FactTable)

  UPDATE OldAndNewValues SET Team_SID = NewTeam_SID

  <smirk>

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 19, 2017 at 8:02 am

  #1951639

  ChrisM@Work - Wednesday, July 19, 2017 7:51 AM

  ;WITH OldAndNewValues AS (

  SELECT Team_SID, NewTeam_SID = MAX(Team_SID) OVER(PARTITION BY Employee_SID)

  FROM dbo.FactTable)

  UPDATE OldAndNewValues SET Team_SID = NewTeam_SID

  <smirk>

  That wouldn't work if an employee changed from say team 54 to 42. 😉

  Thom~

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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 19, 2017 at 8:12 am

  #1951643

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

  ChrisM@Work - Wednesday, July 19, 2017 7:51 AM

  ;WITH OldAndNewValues AS (

  SELECT Team_SID, NewTeam_SID = MAX(Team_SID) OVER(PARTITION BY Employee_SID)

  FROM dbo.FactTable)

  UPDATE OldAndNewValues SET Team_SID = NewTeam_SID

  <smirk>

  That wouldn't work if an employee changed from say team 54 to 42. 😉

  You're absolutely right Thom. In that case, I'd fall back on standard joined update syntax:
  

  UPDATE ft SET

  Team_SID = x.New_Team_SID

  FROM cdw.FactTable ft

  CROSS APPLY (

  SELECT TOP(1)

  New_Team_SID = fti.Team_SID

  FROM cdw.FactTable fti

  WHERE fti.Employee_SID = ft.Employee_SID

  ORDER BY fti.CreationDate DESC

  ) x

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  July 19, 2017 at 8:41 am

  #1951655

  Another way, assuming we're actually on 2012:

  UPDATE ft
SET Team_SID=fv
FROM (SELECT *,fv=FIRST_VALUE(Team_SID) OVER (PARTITION BY Employee_SID ORDER BY CreationDate DESC)
  FROM FactTable) ft
;

  Cheers!

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 20, 2017 at 6:55 am

  #1951815

  And another way:
  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
);
GO
INSERT INTO cdw.FactTable ([SID], Employee_SID, Team_SID, CreationDate)
    VALUES    (N'1', 1001, 40, 20170102),
            (N'2', 1002, 42, 20170102),
            (N'3', 1001, 43, 20170122),
            (N'4', 1003, 44, 20170122),
            (N'5', 1004, 45, 20170122),
            (N'6', 1002, 46, 20170212),
            (N'7', 1001, 40, 20170212),
            (N'8', 1002, 48, 20170222),
            (N'9', 1001, 54, 20170314),
            (N'10', 1003, 50, 20170322),
            (N'11', 1003, 57, 20170407);

  SELECT *
FROM cdw.FactTable;

  WITH NEW_VALUES AS (

      SELECT M.Employee_SID, FT.Team_SID
    FROM (
        SELECT Employee_SID, MAX(CreationDate) AS MAX_DATE
        FROM cdw.FactTable
        GROUP BY Employee_SID
        ) AS M
        INNER JOIN dbo.FactTable AS FT
            ON M.Employee_SID = FT.Employee_SID
            AND M.MAX_DATE = FT.CreationDate
)
UPDATE FT
SET FT.Team_SID = NV.Team_SID
FROM cdw.FactTable AS FT
    INNER JOIN NEW_VALUES AS NV
        ON FT.Employee_SID = NV.Employee_SID;

  SELECT *
FROM cdw.FactTable;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  July 20, 2017 at 9:50 am

  #1951890

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

  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.

  

  

  "No matter how far you have gone down the wrong road, turn around" – Turkish proverb

  Thom A. has told you the truth, and you need to listen to him. Here is Thom's answer with a little more flesh on the bones. I suggest you go over it line by line, study it and learn how to actually write SQL..
   A table models a set of entities, so it's name should be a plural or collective noun. The identifier is never a numeric value (you don't do math on it! What is the square root of your credit card number?). Most of the work in SQL is done in the DDL and not kludge to in the DML like you're trying to do.

  By definition, tables must have a key. But more than that, SQL let you use defaults constraints and references to ensure data integrity. What you have is basically a pile of punch cards or paper forms rewritten into T-SQL. Your datatypes are wrong. Your data element names do not follow ISO 11179 rules. Perhaps a better question is to ask what you think is right in this.

  CREATE TABLE Personnel
(employee_id CHAR(15) NOT NULL PRIMARY KEY,
employment_date DATE NOT NULL,
..);

  CREATE TABLE Teams --Guessed the set up for this table
(team_id CHAR(10) NOT NULL PRIMARY KEY,
..
);

  CREATE TABLE Team_Assignments
(employee_id CHAR(15) NOT NULL
  REFERENCES Personnel
 ON DELETE CASCADE,
team_id CHAR(10) NOT NULL
REFERENCES Teams
ON DELETE CASCADE,
team_assignment_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL
PRIMARY KEY (employee_id, team_id ) );

  See how DRI maintains data integrity? See how REFERENCES work in a schema? 

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• x

  SSC-Insane

  Points: 23660

  More actions

  July 21, 2017 at 11:22 am

  #1952161

  removed

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  July 21, 2017 at 7:16 pm

  #1952226

  jcelko212 32090 - Thursday, July 20, 2017 9:50 AM

  A table models a set of entities, so it's name should be a plural or collective noun.
  

  Here's your chance, Joe.  Post the paragraph and the number of the ANSI standard that says that should be true.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)