This is the error that I got:-
"Recursive common table expression 'DTAGatHERTZ_RO' does not contain a top level Union All operator."
I had tried this:
WITH DTAGatHERTZ_RO
(RO,Time_In,Ranking)AS
(SELECT RO,Time_In, Ranking = DENSE_RANK() OVER (PARTITION BY RO,Time_In ORDER BY NEWID() ASC)
FROM DTAGatHERTZ_RO )
INSERT INTO ROs
SELECT Class, Date_Entered, PMmiles, RO, RoDate, CASE WHEN Ranking = 1 THEN Time_In ELSE DATEADD(mi,Ranking, Time_In) END
from DTAGatHERTZ_RO where RO = '4187'
Please refer to the excel sheet that I had provided for more details on data. Please let me know if you need more info on this.