SQL Server 2016 – Find Minimum Value Greater Than Previous Row

sql serversql-server-2016

How do I find the minimum value greater than the previous row?
This code needs to be optimized to run over million rows and business keys, we may have 3-10 timevalue columns at most. I am thinking its some type of recursive CTE.

background: conducting slowly changing dimensions for many tables which are being denormalized. And trying to find proper begin dates.

Data Sample:

create table dbo.timetest
(
TimeTestIdentityId int primary key identity(1,1),
businesskey int,
timevalue1 int,
timevalue2 int,
timevalue3 int
)

insert into timetest
values
(5,131,134,137),
(5,131,138,135),
(5,131,140,135),
(5,143,141,145),
(5,149,141,148),
(5,150,141,148),

(6,134,137,140),
(6,134,141,138),
(6,134,143,138),
(6,146,144,148),
(6,152,144,151),
(6,153,144,151)

enter image description here

Final Values:

enter image description here

,etc

Row are ordered by TimeValue1.

Select MIN value from multiple columns

Best Answer

My solution using a cursor and a temporary table, I can imagine another solution, but it involves an expensive recursive query.

CREATE TABLE #tv
(
    businesskey int NOT NULL,
    id int NOT NULL,
    timevalue int,
);
GO

DECLARE @bk int, @tv1 int, @tv2 int, @tv3 int;
DECLARE @last_min int = 0;
DECLARE @last_bk int = 0;
DECLARE @id int;

DECLARE curtv CURSOR FORWARD_ONLY READ_ONLY FOR
    SELECT   businesskey, timevalue1, timevalue2, timevalue3
    FROM     timetest
    ORDER BY businesskey, TimeTestIdentityId;

OPEN curtv;
FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;

WHILE @@fetch_status = 0
BEGIN

    -- First row of a businesskey, basically set counters to 0
    IF @last_bk  @bk
    BEGIN
        SET @last_bk = @bk;
        SET @last_min = 0;
        SET @id = 0;
    end

    SET @id = @id + 1;

    -- select minimum positive value
    -- it could be replaced by the below inline UDF but I think it doesn't worth.
    SELECT @last_min = min(v) + @last_min
    FROM   (VALUES (@tv1-@last_min),(@tv2-@last_min),(@tv3-@last_min)) as t(v)
    WHERE  v > 0;

    INSERT INTO #tv VALUES (@bk, @id, @last_min);

    FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;
END

CLOSE curtv;
DEALLOCATE curtv;
GO

-- one temp table has been generated you could add an index.
ALTER TABLE #tv ADD CONSTRAINT [PK_tv] PRIMARY KEY (businesskey, id);
GO

SELECT * FROM #tv ORDER BY businesskey, id;
GO

IF OBJECT_ID('tempdb..#tv') IS NOT NULL DROP TABLE #tv;
GO
businesskey | timevalue
----------: | --------:
          5 |       131
          5 |       135
          5 |       140
          5 |       141
          5 |       148
          5 |       150
          6 |       134
          6 |       138
          6 |       143
          6 |       144
          6 |       151
          6 |       153

db<>fiddle here

Using a recursive CTE:

IF OBJECT_ID('min_positive') IS NOT NULL
    DROP FUNCTION min_positive;
GO

-- inline UDF you should add as many parameters as you need.
CREATE FUNCTION min_positive(@v1 int, @v2 int, @v3 int)
RETURNS TABLE AS
RETURN
    SELECT MIN(v) AS minpos
    FROM (VALUES (@v1),(@v2),(@v3)) AS t(v)
    WHERE v > 0;
GO

;WITH ct1 AS 
(
    -- set a rank for each businesskey
    -- I need it for the anchor in the recursive part
    SELECT businesskey, timevalue1, timevalue2, timevalue3,
           RANK() OVER (PARTITION BY businesskey ORDER BY TimeTestIdentityId) rk
    FROM   timetest
)
, ct2 AS
(
    -- first row of each businesskey
    SELECT businesskey, timevalue1, timevalue2, timevalue3, rk,
           (SELECT minpos
            FROM   min_positive(timevalue1, timevalue2, timevalue3)) AS lm
    FROM ct1
    WHERE rk = 1

    UNION ALL

    -- next row of the businesskey untill the end of the rank
    SELECT ct1.businesskey, ct1.timevalue1, ct1.timevalue2, ct1.timevalue3, ct1.rk,
           (SELECT minpos + ct2.lm
            FROM min_positive(ct1.timevalue1-ct2.lm, ct1.timevalue2-ct2.lm, ct1.timevalue3-ct2.lm)) lm
    FROM ct1
    JOIN ct2
         ON ct2.businesskey = ct1.businesskey
         AND ct1.rk = ct2.rk + 1
)
SELECT   businesskey, lm 
FROM     ct2 
ORDER BY businesskey, rk;
GO

db<>fiddle here

Related Question