January 13, 2021 at 6:20 pm
I have a large employee table with lots of columns and versions and one org lookup table also with some versions.
I need to update a column org_id in employee table which is all nulls now with no history.
An employee can have multiple versions records each with same empid but same or different rc_code.
table structure
Employee—
Emp_id, emp_ver_id(pk) ,as_of_date, end_date,rc_code, org_id
Emp_ver_id is identity.
Org —
Org_ver_id(pk), Org_id , Rc_code, as_of_date, end_date
org_ver_id is identity
I need to update all org_id under each employee records with the earliest rc_code/ org_id for that employee based on as_of_date which is earliest for that employee record.
problem : due to row by row operation lookup between 2 tables on rc code gives different org_id value which is not the earliest one.
sample result needed is below -
Emp_ver_id,Emp_id,org_id,as_of_date,Rc_code
1. 1 22. 01/01/2020. 01
2. 1. 22. 02/01/2020. 03
3. 1. 22 03/01/2020. 04
4. 2. 32 01/01/2019. 13
5. 2. 32 04/09/2019. 14
January 13, 2021 at 7:19 pm
With DDL and sample data, I can only guess at using row_number over/partition clause
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 13, 2021 at 9:49 pm
For example, please provide us the table structure in code and sample data in code form, as INSERT statements:
IF OBJECT_ID('tempdb.dbo.#Employee') IS NOT NULL
DROP TABLE #Employee;
CREATE TABLE #Employee (
emp_ver_id int IDENTITY(1, 1) NOT NULL,
emp_id int NOT NULL,
org_id int NULL,
as_of_date date NULL,
rc_code char(2) NULL
)
INSERT INTO #Employee VALUES
( ... ), /* !!put your sample data here!! */
( ... ) /*, ...*/
IF OBJECT_ID('tempdb.dbo.#Org') IS NOT NULL
DROP TABLE #Org;
CREATE TABLE #Org (
org_ver_id int IDENTITY(1, 1) NOT NULL,
Org_id int NOT NULL,
Rc_code char(2) NULL,
as_of_date date NULL,
end_date date NULL
)
INSERT INTO #Org VALUES
( ... ), /* !!put your sample data here!! */
( ... ) /*, ...*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy