Recursive CTE (Sqlite) for generate

In large modern databases you usually have a function available called generate_series that is useful for creating an inline series numbers, dates, etc

Example

SELECT * FROM generate_series(2,4);

Produces the results of

generate_series
-----------------
2
3
4

You can also combine this with dates and date functions to create a set of dates which is useful when finding overlaps.

However I have a Sqlite database and table with a structure like this

CREATE TABLE RateTable
(
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	external_id varchar(20),
	start TEXT,
	end TEXT,
	base_amount DECIMAL(18,6),
	guest_count int,
	file_id int,
	FOREIGN KEY (file_id) REFERENCES FileInfo(id) ON DELETE CASCADE,
	UNIQUE(external_id, file_id, start, end)
)

Now I want to use a between clause to search if a day in a series of days exists between the start, end dates in the above table (Sqlite uses the type TEXT for datetimes)

What I want is to generate a series of dates and then join them against this table and if each individual date exists between that boundary then select that data row

As in I have dates 2024-01-26 to 2024-02-05 and I want to create a set of of the 10 days between the above input dates and then use that particular date to compare with a between on the joined table something like this

<SERIES_GENERATED_DATE> between RateTable.start and RateTable.end

To do this I came up with using a recursive Common Table Expression

Something like this

WITH date_range(myDate, nights) as
(
	SELECT DATE('2024-01-26', '+1 day') as myDate, 1 as nights
	UNION ALL
	SELECT DATE(myDate, '+1 day'), nights + 1 as nights
	FROM date_range
	WHERE nights < (SELECT CAST(JULIANDAY('2024-02-05') - JULIANDAY('2024-01-26') as INTEGER))
)
SELECT *
FROM date_range;

I'm adding a 1 to the start to make the dates offset and not use a 0 based index (it works better in my particular use case, you can change it as needed).

Produces this

myDate, nights
2024-01-27	1
2024-01-28	2
2024-01-29	3
2024-01-30	4
2024-01-31	5
2024-02-01	6
2024-02-02	7
2024-02-03	8
2024-02-04	9
2024-02-05	10

And that works, but also I did an alternative recursive CTE as well like so using a limit and putting the date function on the end. Right now I'm going with this second one only because I like the use of the limit termination clause instead of the where

WITH RECURSIVE cnt(x) AS
(
	SELECT 1
	UNION ALL
	SELECT x + 1
	FROM cnt
	LIMIT (SELECT (JULIANDAY('2024-02-05') - JULIANDAY('2024-01-26')))
)
SELECT date(JULIANDAY('2024-01-26'), '+' || x || ' days') as td, x
FROM cnt;

Now that I have a set of dates in generated series I can join them with the original table to find all the applicable overlaps like so

select o.external_id,
	   date(aa.td, '-1 day') current_day,
	   o.start,
	   o.end,
	   o.base_amount
from RateTable o
inner join
(
	WITH RECURSIVE cnt(x) AS
	(
		SELECT 1
		UNION ALL
		SELECT x + 1
		FROM cnt
		LIMIT (SELECT (JULIANDAY('2024-02-05') - JULIANDAY('2024-01-26')))
	)
	SELECT date(JULIANDAY('2024-01-26'), '+' || x || ' days') as td, x
	FROM cnt
) aa
on aa.td between o.start and o.end;

Produces the results I was looking for in the case

external_id	current_day	start		end			base_amount
id123456789	2024-01-26	2024-01-26	2024-01-27	449	
id123456789	2024-01-27	2024-01-27	2024-01-28	364	
id123456789	2024-01-28	2024-01-28	2024-02-02	320	
id123456789	2024-01-29	2024-01-28	2024-02-02	320	
id123456789	2024-01-30	2024-01-28	2024-02-02	320	
id123456789	2024-01-31	2024-01-28	2024-02-02	320	
id123456789	2024-02-01	2024-01-28	2024-02-02	320	
id123456789	2024-02-02	2024-02-02	2024-02-03	383	
id123456789	2024-02-03	2024-02-03	2024-02-04	391	
id123456789	2024-02-04	2024-02-04	2024-02-09	320