Myujth

I am trying to do a row calculation whereby the larger value will carry forward to the subsequent rows until a larger value is being compared. It is done by comparing the current value to the previous row using the lag() function.

Code

DECLARE @TAB TABLE (id varchar(1),d1 INT , d2 INT)

INSERT INTO @TAB (id,d1,d2) 

VALUES ('A',0,5)
 ,('A',1,2)
 ,('A',2,4) 
 ,('A',3,6)
 ,('B',0,4)
 ,('B',2,3)
 ,('B',3,2)
 ,('B',4,5)

SELECT id
 ,d1
 ,d2 = CASE WHEN id <> (LAG(id,1,0) OVER (ORDER BY id,d1)) THEN d2
 WHEN d2 < (LAG(d2,1,0) OVER (ORDER BY id,d1)) THEN (LAG(d2,1,0) OVER (ORDER BY id,d1)) 
 ELSE d2 END

Output (Added row od2 for clarity)

 +----+----+----+ +----+
 | id | d1 | d2 | | od2|
 +----+----+----+ +----+
 | A | 0 | 5 | | 5 |
 | A | 1 | 5 | | 2 |
 | A | 2 | 4 | | 4 |
 | A | 3 | 6 | | 6 |
 | B | 0 | 4 | | 4 |
 | B | 2 | 4 | | 3 |
 | B | 3 | 3 | | 2 |
 | B | 4 | 5 | | 5 |
 +----+----+----+ +----+

As you can see from the output it lag function is referencing the original value of the previous row rather than the new value. Is there anyway to achieve this?

Desired Output

 +----+----+----+ +----+
 | id | d1 | d2 | | od2|
 +----+----+----+ +----+
 | A | 0 | 5 | | 5 |
 | A | 1 | 5 | | 2 |
 | A | 2 | 5 | | 4 |
 | A | 3 | 6 | | 6 |
 | B | 0 | 4 | | 4 |
 | B | 2 | 4 | | 3 |
 | B | 3 | 4 | | 2 |
 | B | 4 | 5 | | 5 |
 +----+----+----+ +----+

Try this:

SELECT id
 ,d1
 ,d2 
 ,MAX(d2) OVER (PARTITION BY ID ORDER BY d1)
FROM @TAB 

The idea is to use the MAX to get the max value from the beginning to the current row for each partition.

Thanks for providing the DDL scripts and the DML.

One way of doing it would be using recursive cte as follows.
1. First rank all the records according to id, d1 and d2. -> cte block
2. Use recursive cte and get the first elements using rnk=1
3. the field "compared_val" will check against the values from the previous rnk to see if the value is > than the existing and if so it would swap

DECLARE @TAB TABLE (id varchar(1),d1 INT , d2 INT)

INSERT INTO @TAB (id,d1,d2) 

VALUES ('A',0,5)
 ,('A',1,2)
 ,('A',2,4) 
 ,('A',3,6)
 ,('B',0,4)
 ,('B',2,3)
 ,('B',3,2)
 ,('B',4,5)

;with cte
 as (select row_number() over(partition by id order by d1,d2) as rnk
 ,id,d1,d2 
 from @TAB 
 )
 ,data(rnk,id,d1,d2,compared_val)
 as (select rnk,id,d1,d2,d2 as compared_val
 from cte
 where rnk=1
 union all
 select a.rnk,a.id,a.d1,a.d2,case when b.compared_val > a.d2 then 
 b.compared_val 
 else a.d2
 end
 from cte a
 join data b
 on a.id=b.id
 and a.rnk=b.rnk+1
 )
select * from data order by id,d1,d2