1

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

3

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

1
with  
cte1 (businesskey,timevalue1,timevalue2,timevalue3,rn) as (
select businesskey,timevalue1,timevalue2,timevalue3,
       row_number() over (partition by businesskey order by timevalue1,timevalue2,timevalue3) rn
from timetest
),
cte2 (businesskey,timevalue1,timevalue2,timevalue3,rn,timevalue0) as (
select businesskey,timevalue1,timevalue2,timevalue3,rn,timevalue1
from cte1
where rn=1
union all
select t2.businesskey,t2.timevalue1,t2.timevalue2,t2.timevalue3,t2.rn,
       case when t2.timevalue1>t1.timevalue0 then t2.timevalue1 else 2147483647 end
from cte2 t1
inner join cte1 t2 on t1.businesskey=t2.businesskey
          and t1.rn+1=t2.rn
union all
select t2.businesskey,t2.timevalue1,t2.timevalue2,t2.timevalue3,t2.rn,
       case when t2.timevalue2>t1.timevalue0 then t2.timevalue2 else 2147483647 end
from cte2 t1
inner join cte1 t2 on t1.businesskey=t2.businesskey
          and t1.rn+1=t2.rn
union all
select t2.businesskey,t2.timevalue1,t2.timevalue2,t2.timevalue3,t2.rn,
       case when t2.timevalue3>t1.timevalue0 then t2.timevalue3 else 2147483647 end
from cte2 t1
inner join cte1 t2 on t1.businesskey=t2.businesskey
          and t1.rn+1=t2.rn
)
select businesskey,timevalue1,timevalue2,timevalue3,rn,min(timevalue0) timevalue0 
from cte2 
group by businesskey,timevalue1,timevalue2,timevalue3,rn
order by 1,5;

fiddle

Output:

businesskey timevalue1  timevalue2  timevalue3  rn  timevalue0
5           131         134         137         1   131
5           131         138         135         2   135
5           131         140         135         3   140
5           143         141         145         4   141
5           149         141         148         5   148
5           150         141         148         6   150
6           134         137         140         1   134
6           134         141         138         2   138
6           134         143         138         3   143
6           146         144         148         4   144
6           152         144         151         5   151
6           153         144         151         6   153

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy