Split mile limits to the thousandth based on ID
If you don't want to use a recursive CTE you could use below query example:
SELECT m.ID,
a.rn as start_mile,
a.rn+ CONVERT(decimal(15, 3),0.001) as end_mile
FROM dbo.Miles m
CROSS APPLY
(
SELECT TOP(CONVERT(int,(( end_mile - Start_mile)* 1000)))
CONVERT(decimal(15, 3),(Start_mile + (CONVERT(decimal(15, 3),ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1) / 1000))) as rn
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
) as a;
This should work up until 6M miles for one id.
The spt values table could be replaced by a numbers table of some sort.
DB<>Fiddle
You just need to reference the initial starting and ending values in the anchor.
CREATE TABLE #tmp (
ID INT NOT NULL PRIMARY KEY
,[Start] DECIMAL(15, 3)
,[To] DECIMAL(15, 3)
)
GO
INSERT INTO #tmp (
ID
,[Start]
,[To]
)
VALUES (
1
,5.00
,5.99
)
,(
2
,10.00
,10.4
);
WITH cte
AS (
SELECT ID
,[Start]
,[To]
,CAST([Start] AS DECIMAL(15, 3)) AS [Value]
FROM #tmp
UNION ALL
SELECT ID
,[Start]
,[To]
,CAST(([Value] + 0.001) AS DECIMAL(15, 3))
FROM cte
WHERE CAST(([Value] + 0.001) AS DECIMAL(15, 3)) < [To]
)
SELECT *
FROM cte
ORDER BY ID
,Start
,Value
OPTION (MAXRECURSION 0)
This is one way to do it. This is only one way. There are multiple other ways to do this, and every way will involve some looping and tradeoffs, but once this way is prepared, no other looping is needed.
CREATE TABLE #Intervals (
i decimal(7,3) not null primary key
)
INSERT INTO #Intervals Values (0.001), (0.002), (0.003), (0.004), (0.005), (0.006),
(0.007), (0.008), (0.009), (0.01), (0.011), (0.012), (0.013), (0.014), (0.015), (0.016)
DECLARE @ct int = 18 -- Up to 4100 miles. Reduce iterations if you don't need this much.
DECLARE @mx decimal(7,3)
SELECT @mx = Max(i) from #Intervals
WHILE @ct > 0 -- loop for loading the #Intervals table
BEGIN
INSERT INTO #Intervals SELECT i + @mx FROM #intervals
SELECT @mx = Max(i) from #Intervals
SET @ct -=1
END
INSERT INTO #Intervals Values(0.000) -- just in case milepost zero
CREATE TABLE #miles (
ID int not null,
Start_Mile decimal(7,3) Not Null,
End_Mile decimal(7,3) Not Null
)
CREATE TABLE #NewMiles (
ID int not null,
Start_Mile decimal(7,3) Not Null,
End_Mile decimal(7,3) Not Null
)
INSERT INTO #miles Values
(1, 5.23, 7.464),
(2, 2.333, 6.124)
INSERT INTO #NewMiles
SELECT m.id, i.i, i.i + 0.001
FROM #miles m
INNER JOIN #Intervals i
ON i.i >= m.Start_Mile and i.i < m.End_Mile
SELECT *
FROM #NewMiles
ORDER BY id, Start_Mile
DROP TABLE #Intervals
DROP TABLE #miles
DROP TABLE #NewMiles
Other possible methods include a true numbers table (values from 0 to whatever) with additional calculations to determine the top cutoff, a version of your CTE embedded into a table-valued function like this:
CREATE FUNCTION dbo.GetMileThousandths (
@ID int,
@from decimal(7, 3),
@to decimal(7, 3)
)
RETURNS TABLE
AS
RETURN (
WITH cte AS (
SELECT @id as id,
@from AS Value
UNION ALL
SELECT id,
Cast(Value + 0.001 as decimal(7,3))
FROM cte
WHERE Value < @to
)
SELECT *
FROM cte
)
GO
SELECT id, Value, Value+0.001
FROM dbo.GetMileThousandths(1, 1.111, 2.222)
OPTION (maxRecursion 0)
and many others