Lead function with duplicate dates?-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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