I have a table of IDs and dates. I need to turn the dates into datetimes, but each timestamp has to be unique within that ID. For example, if this is my table:
| ID | Date |
|---|---|
| 0001 | 2020-03-21 |
| 0001 | 2020-03-21 |
| 0001 | 2020-03-21 |
| 0002 | 2020-03-21 |
| 0002 | 2020-03-21 |
then I need my output to look like:
| ID | Datetime |
|---|---|
| 0001 | 2020-03-21 00:00:01 |
| 0001 | 2020-03-21 00:00:02 |
| 0001 | 2020-03-21 00:00:03 |
| 0002 | 2020-03-21 00:00:01 |
| 0002 | 2020-03-21 00:00:02 |
I'm pretty sure we can safely assume that no ID will have 86,400 entries on one day, so I shouldn't have to worry about the date rolling over by accident.
I'm thinking a cursor may be the answer, but I'm new to SQL and haven't had much luck figuring out how I'd use one for this. Here's what I tried so far, which didn't get me quite where I need to be:
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC) AS RN
,[CID]
,[XDATE]
,dateadd(second, ((ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC))