I am trying to convert the sql server recursive CTE query to databricks spark equivalent.

Here is my code.

with
hour_data AS 
(
  SELECT name,ID, OperatorId, 
     FlooredStartTime, FlooredEndTime, 
     StartTime,EndTime , 
     HourBucket, LI_HourBucket,
     cast(HourBucket as datetime) as NextStartTime,
     DATEADD(HOUR,1,Hourbucket) as NextEndTime
    FROM loginlogout1 as l

  UNION ALL

  SELECT  sitename,ZoneID, OperatorId, 
     FlooredStartTime, FlooredEndTime, 
     StartTime,EndTime, 
     LO_HourBucket, LI_HourBucket,
     DATEADD(HOUR,1,NextStartTime) as NextStartTime ,
     DATEADD(HOUR,1,NextEndTime) as NextEndTime
   FROM hour_data
   WHERE NextEndTime < FlooredEndTime
)
select * from hour_data 

I reffered some links it does not work for me this use case.

Appreciate if someone help.

Tried with the looping on databricks and some links with udf’s. does not work for me.