Fill in Times between Dates, can it be done without a recursive cte

Question

The below code gives me the correct result but in sql could it be done without a recursive cte? Maybe a cross apply.

declare @t table
(locationID int,
 StartTime datetime,
 EndTime datetime
)

 insert @t
 values (1,'1900-01-01 08:00','1900-01-01 19:30')
 ;
 insert @t
 values (2,'1900-01-01 10:00','1900-01-01 15:00')
 ;
 insert @t
 values (3,'1900-01-01 09:30','1900-01-01 21:00')
 ;
with cte_t
as
(
  select locationID, StartTime, EndTime, StartTime as [Time]
  from @t
 union all
  select locationID, StartTime, EndTime, dateadd(mi,30,[Time]) as [Time]
  from cte_t
  where [Time] < [EndTime]
)

select * from cte_t order by locationID

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c1ffdd41faaaa631a11877bce65f982f — Roger Clerkwell, May 11 '19 at 06:52
Recursive CTE returning 59 row where as CROSS APPLY only 9. Which one is expected? — mkRabbani, May 11 '19 at 08:08
OK thanks Larnu so you mean like this example first create the times in a cte and join. — Roger Clerkwell, May 11 '19 at 08:28
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=254c23fa99ddb9543fd233e0e5362073 — Roger Clerkwell, May 11 '19 at 08:29
sorry mkRabbani I don't understand where is the 9 rows from the cross apply coming from? — Roger Clerkwell, May 11 '19 at 08:31
With a physical table [like this](https://stackoverflow.com/a/32474751/5089204) you will have a very handsome list of values you can use in various scenarios. A pure numbers table is fine, but you can add a lot of addtional information to this... — Shnugo, May 13 '19 at 06:45

score 0 · Answer 1 · answered May 11 '19 at 08:35

with sample_data_table
as
(
    select locationID,cast(StartTime as datetime) as StartTime,cast(EndTime as datetime) EndTime
    from (values (1,'1900-01-01 08:00','1900-01-01 19:30')
                ,(2,'1900-01-01 10:00','1900-01-01 15:00')
                ,(3,'1900-01-01 09:30','1900-01-01 21:00')
    ) t(locationID,StartTime,EndTime)
)

, cte_times AS
(
 SELECT  TOP ((DATEDIFF(HOUR , cast('19000101' as datetime), cast('19000102' as datetime))*2))
 ROW_NUMBER() OVER (ORDER BY number) AS rownum,
 DATEADD(minute, (ROW_NUMBER() OVER (ORDER BY number) -1)*30, cast('19000101' as datetime)) AS dt
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' 
)

 select a.*, b.*
 from sample_data_table as a
 inner join cte_times as b on a.StartTime <= b.dt and a.EndTime >= b.dt
 order by a.locationID,b.dt

Tally Table...like I have my example above? so there are only two possible ways rCTE and Tally ( and I guess loop). — Roger Clerkwell, May 11 '19 at 08:36
Just search the internet - [numbers table](https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) — SMor, May 11 '19 at 11:49

Fill in Times between Dates, can it be done without a recursive cte

1 Answers1