update column based on max date for each employee
-
Eirikur Eiriksson - Wednesday, July 19, 2017 6:05 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>
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
ChrisM@Work - Wednesday, July 19, 2017 7:51 AM
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 -
Thom A - Wednesday, July 19, 2017 8:02 AM
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 ShawFor 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 -
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!
-
And another way:
SET QUOTED_IDENTIFIER ON
GOCREATE 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy) -
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
GOCREATE 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.
-
removed
-
jcelko212 32090 - Thursday, July 20, 2017 9:50 AM
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)
Intro to Tally Tables and Functions
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply