Hi,
I have a table table1 having the details as below
RowID ID Test_BEGDT Test_ENDDT Test1_BEGDT Test1_ENDDT
1 220041 4/13/1998 3/15/2020 1/1/2014 12/31/2020
2 220041 4/13/1998 12/31/9999 1/1/2014 12/31/2020
3 220041 4/13/1998 12/31/9999 1/1/2015 12/31/9999
4 220041 4/13/1998 12/31/9999 12/31/9999 12/31/9999
i need to delete all the duplicates for the ID and want only a single record in the table.
The Dates vary for each ID and are not constant.
i tried using the below code but the wrong record is remaining.
i need the record having TEST1_BEGDT=1/1/2015 should be remained after removing all the other duplicates because it is my latest record
WITH CTE(RowNumber, Sequence, ID ,Test_BEGDT, Test_ENDDT, Test1_BEGDT, Test1_ENDDT ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID, TEST_BEGDT, TEST1_BEGDT order by ID, TEST_BEGDT, TEST1_BEGDT ) AS RowNumber,
ROW_NUMBER() over (PARTITION BY id order by id) as Sequence
, ID
, TEST_BEGDT
,TEST_ENDDT
, TEST1_BEGDT
, TEST1_ENDDT
FROM table1 tbl
)
delete from CTE where Sequence > 1
after running the above query my result set looks like below from which allthe records greater than sequence 1 are being deleted
Row sequence id test_begdt test_enddt test1_begdt test1_enddt
1 1 220041 1998-04-13 2020-03-15 2014-01-01 2020-12-31
2 2 220041 1998-04-13 9999-12-31 2014-01-01 2020-12-31
1 3 220041 1998-04-13 9999-12-31 2015-01-01 9999-12-31
1 4 220041 1998-04-13 9999-12-31 9999-12-31 9999-12-31
i have to delete the records 1,2,4 and make sure 3 record is present in the table
since the 1 and 2 records are duplicates and 4th record have default test1_begdt date(9999-12-31)
please suggest
when i try and