Hi,
I'm having a problem on how to get the records if the duplicate ID the date difference is 4 days and get the latest ID. any help is very much appreciated. see below sample DDL as reference and desired result.
Declare @sample table
(id nvarchar (50), dt datetime, itemid varchar(35)
)
insert @sample values ('ABCDE5B12AG123AA','2017-07-29 05:03:00.000','SAL00A')
insert @sample values ('ABCDE5B12AG123AA','2017-07-02 15:42:00.000','SAL00A')
insert @sample values ('ABCDE5B12AG123TB','2017-10-25 02:03:00.000','SAL001')
insert @sample values ('ABCDE5B12AG123TB','2017-10-29 04:42:00.000','SAL001')
insert @sample values ('ABCDE5B12AG12345','2017-02-27 20:26:00.000','SAL001')
insert @sample values ('ABCDE5B12AG12345','2017-02-28 00:01:00.000','SAL001')
insert @sample values ('ABCDE5B12AG33333','2017-02-02 04:01:00.000','BOI002')
insert @sample values ('ABCDE5B12AG11111','2017-11-26 10:03:09.000','TOP222')
insert @sample values ('ABCDE5B12AG11111','2017-10-25 02:03:00.000','TOP222')
insert @sample values ('ABCDE5B12AG11111','2017-10-29 04:42:00.000','TOP222')
select * from @sample
result
id------------------dt----------------------itemid
ABCDE5B12AG123AA----2017-07-29 05:03:00.000--SAL00A
ABCDE5B12AG123AA----2017-07-02 15:42:00.000--SAL00A
ABCDE5B12AG123TB----2017-10-29 04:42:00.000--SAL001
ABCDE5B12AG12345----2017-02-28 00:01:00.000--SAL001
ABCDE5B12AG33333----2017-02-02 04:01:00.000--BOI002
ABCDE5B12AG11111----2017-11-26 10:03:09.000--TOP222
ABCDE5B12AG11111----2017-10-29 04:42:00.000--TOP222