looking for a solution to this....

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

SQL Server 2000 Forums

Transact-SQL (2000)

looking for a solution to this....

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-24 : 11:27:02

I have 4 tables (tmpcurHRMIS,tmpcurHRMIS1,tmpcurHRMIS2,tmpcurHRMIS3).

All tables have exactly same structre (31 columns).
[Extracted, Unit_Coll, Pos_Number, Post_Date, Comm_Servi, Effe_Servi, Old_Reg, Rank_date, ERank, Surname, Initials, Sex, First_Lang, Read_Date, Write_Date, Oral_Date, Psreg, Regimental,FUC, Empl_Class, Years_serv, Memb_1, Memb_2, Memb_3, Memb_4, Memb_5, Pos_Status, B_Empl, Emp_Stat, Action, Act_Reason, Time_Ingrc, Time_Inpos, Time_Inccs, Empl_Id]

tmpcurHRMIS is the master table with 45280 records and should have 45183 records (+97 records.

tmpcurHRMIS1 is a subset of tmpcurHRMIS and has 4066 records.

tmpcurHRMIS1 is created with the statement:
Select *
from tmpCurhrmis
where left(surname,10) = '*** VACANT'

tmpcurHRMIS2 is a subset of tmpcurHRMIS and has 41214 records.

tmpCurhrmis2 is created with the statement:
select *
from tmpCurhrmis
where left(surname,10) <> '*** VACANT'

tmpcurHRMIS3 is a subset of tmpcurHRMIS2 and has 41214 and should have 41117 (+97) records.

tmpCurhrmis3 is created with the statement:
select *
from tmpCurhrmis2

There are 97 records in tmpcurhrmis and tmpcurhrmis3 that have multiple records for the same [regimental] column. I need to grab the most recent record where there are mulitple [regimental] based on rank_date. Is there a way to do this?

Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-24 : 19:59:28

First, I have to tell you that whenever someone starts talking about multiple tables with identical structure (and especially almost identical names) alarms start going off in my head. I would strongly encourage you to re-think this strategy.

Now, to get rid of the duplicates, do a SELECT ... GROUP BY regimental HAVING COUNT(*) > 1 to identify the PKs of the 194 (97x2) rows that have duplicates. Then you can start working on eliminating the wrong records. And read Graz's article on deleting duplicates.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources