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

Tags:

Sql Server