Dynamically Update Coulmn

Transact-SQL
,
1

Hi There,

I want to update a column dynamically based on the other columns data in the same table, based on the below rules .

I have done the activity in excel, but unable to code it.

Test_Scenario
Test_Scenario.JPG947×641 72.3 KB

sample data image is attached.

Columns :

  1. Promo ID: From System,
  2. Scheme ID: From System ( One Promo ID can have multiple Scheme ID )
  3. Min_Val: From System
  4. Max_Val: to be updated based on the above 3 columns

Rule :
Max_Val =
IF: (the next row Promo ID is the same then take the Min_Val -1 of it )
THEN
Min_Val - 1
ELSE
500

in Excel : =IF(A2=A3,C3-1,500)

Regards,
Adil

2

You can use LEAD/LAG to get next/previous values in SQL Server 2012 and higher:

Declare @Promotions Table (
        PromoID int 
      , SchemeID int
      , MinValue int);

 Insert Into @Promotions (PromoID, SchemeID, MinValue)
 Values (1, 10, 30)
      , (1, 20, 90)
      , (1, 30, 200)
      , (2, 40, 20)
      , (3, 50, 20)
      , (4, 60, 20)
      , (5, 70, 20)
      , (5, 80, 50)
      , (5, 90, 80)
      , (6, 100, 100)
      , (7, 110, 7)
      , (7, 120, 20)
      , (7, 130, 50)
      , (8, 140, 20);

 Select *
      , MaxValue = lead(MinValue - 1, 1, 500) over(Partition By PromoID Order By SchemeID)
   From @Promotions
1 Like
3

Thanks a lot , Issue resolved .

Regards,
Adil