In SQL Server, I have an interesting problem. I am randomizing data for a test database. The requirement is: For every row in Record table, randomize the day and month of Occurrence Date, which is a date column. If the row belongs to a group of more than one RecordSeriesId, keep the order of the Occurrence Dates, but randomize the day and month. The end result should like as below after randomizing, which the year being the same year as the original. The table also has a INT incremental unique identifier.

My issue is with randomizing the day and month while keeping the year as well as maintaining the actual order of the records. I need a script to go through the entire table and update to randomize the days and months, but maintain the OccurrenceDate order for records with the same RecordSeriesId.

The farthest I have gotten is randomizing dates in the same year as the original occurrence date, but this does not account for previous rows in the series, and I do not know how to go about solving this problem.

WITH RandomizedDates AS (
  SELECT Id,
    RecordSeriesId,
    OccurrenceDate,
    ROW_NUMBER() OVER (PARTITION BY RecordSeriesId ORDER BY OccurrenceDate) AS RowNumber,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 30, DATEADD(MONTH, ABS(CHECKSUM(NEWID())) % 12, DATEFROMPARTS(YEAR(OccurrenceDate), 1, 1))) AS RandomizedDate
  FROM 
    Record
    where RecordSeriesId = 3319
)
SELECT Id,
  RecordSeriesId,
  RandomizedDate
FROM 
  RandomizedDates
ORDER BY 
  RecordSeriesId,
  RandomizedDate;