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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply