Update group by changed records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update group by changed records

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-11-05 : 10:35:20

I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))

DECLARE @Records TABLE(
firstname varchar(50), lastname varchar(50), birthdate date, sex char(1), IDNum varchar(15), moddate date, opflag char(1))
INSERT INTO @Records
VALUES
('JON','SMITH','20000101','M','12345','20140101','I'),
('JON','SMITH','20000101','M','12345','20140201','I'),
('JON','SMITH','20000102','M','12345','20140301','I'),
('JANE','SMITH','20010101','F','54321','20140401','I'),
('JANE','SMITH','20010101','F','54321','20140501','I'),
('JANE','SMITH','20010102','F','54321','20140505','I'),
('BILL','JONES','20020101','M','23456','20140501','I'),
('BILL','JONES','20020101','M','23456','20140601','I'),
('BILL','JONES','20020101','F','23456','20140701','I'),
('AMY','LEE','20030101','F','9876','20140501','I'),
('AMY','LEE','20030101','F','9876','20140601','I')

DECLARE @EmpTemp TABLE(
firstname varchar(50), lastname varchar(50), birthdate date, sex char(1), IDNum varchar(15))
INSERT INTO @EmpTemp
SELECT firstName,LastName,birthDate,sex,IDNum
FROM @Records
GROUP BY firstName,LastName,birthDate,sex,IDNum

update @Records
set opflag = 'X' where IDNum in(
select IDNum from @EmpTemp
GROUP BY IDNum
having count(IDNum) >1)

update R1
set opflag = 'I'
From @Records R1
where R1.opflag = 'X'
and R1.moddate = (select MAX(R2.moddate) from @Records R2 where R1.IDNum = R2.IDNum group by R2.IDNum)

select * from @Records

--Sorry for the terrible subject, had trouble wording it.

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-11-05 : 11:23:57

So I figured a business rule around this, essentially I update all records to X where the ID is the same and has a more recent modified date.

UPDATE P1
SET opflag= 'X'
FROM @Records P1
WHERE EXISTS (
Select 1 from @Records P2
where P1.IDNum = p2.IDNum
and p1.moddate < p2.moddate
)

Not sure if I should delete the question or if there's some value in leaving it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources