Lead function with duplicate dates?

Question

I am looking at a legacy payment system and some of the payments have been input with the exact same datetime (to the second) (see the attached image)

What I am trying to achieve is in these instances, add a second to one of them (doesn't matter which) just so I can differentiate the date. I have tried this with a lead function however as both dates are the exact same, it is just going straight to the date after and ignoring the duplicated date.

My lead function looks like this

CASE WHEN p.DateCreated = LEAD(DateCreated, 1,0) OVER (partition by TransactionID ORDER BY DateCreated) THEN DATEADD(SECOND,1,DateCreated) ELSE P.DateCreated end [DateCreated]

Does anyone know the best way to achieve this? Thinking it might just be something simple I am overlooking.

Answer 1

Try This with CTE and PARTITION BY

;WITH cte AS
(
  SELECT
      ROW_NUMBER() OVER(PARTITION BY Lead_Date ORDER BY Lead_Date ) AS rno,
      Lead_Date 
  FROM yourTable
)

UPDATE cte SET Lead_Date =  DATEADD(ss,1,Lead_Date)
WHERE rno=2

This query should add 1 second to Lead_Date in duplicate recordings.

Answer 2

Here is an example using LAG. NB: If there are 3 identical values you will need to run it twice, and so on.

CREATE TABLE P (id int, dateCreated datetime);

insert into p values
(1,'2022-03-03 10:30:00'),
(2,'2022-03-03 10:30:00'),
(3,'2022-03-03 10:30:10');

SELECT * FROM p;

UPDATE p set dateCreated =  DATEADD(SECOND,1,DateCreated)
FROM 
P JOIN 
 ( SELECT ID, 
 dateCreated d1 ,  
 LAG (dateCreated) over(order by dateCreated) d2 from p) P2
ON p.ID = p2.id
WHERE dateCreated=d2;

SELECT * FROM p;

id | dateCreated            
-: | :----------------------
 1 | 2022-03-03 10:30:00.000
 2 | 2022-03-03 10:30:00.000
 3 | 2022-03-03 10:30:10.000

id | dateCreated            
-: | :----------------------
 1 | 2022-03-03 10:30:00.000
 2 | 2022-03-03 10:30:01.000
 3 | 2022-03-03 10:30:10.000

db<>fiddle here

Answer 3

As far I have understood your question there is only one thing that is missing in your case when statement which is "an and operator along with a LeadDate condition". Please try the below sample and compare that with your case when.

Edit: Query updated to handle multiple duplicate records of DateCreated and LeadDate

declare @tbl table(TransactionId int, DateCreated datetime, LeadDate datetime)

insert into @tbl
values(277090, '2020-04-13 18:50:17.580','2020-04-13 18:50:16.580')
,(277090, '2020-04-13 18:50:17.580','2020-04-13 18:50:16.580')
,(277090, '2020-04-13 18:50:17.580','2020-04-13 18:50:16.580')
,(277090, '2020-04-13 18:50:16.580','1900-01-01 00:00:00.000')

select * from @tbl

;with cte as(
select TransactionId,DateCreated,LeadDate
,ROW_NUMBER()over(partition by TransactionId order by LeadDate)rownum
from @tbl
)
select TransactionId,DateCreated
, case when lead(DateCreated,1,0)over(partition by TransactionId order by DateCreated) = DateCreated
and lead(LeadDate,1,0)over(partition by TransactionId order by LeadDate) = LeadDate
then DATEADD(SECOND,rownum,LeadDate) else LeadDate end as LeadDate
from cte