How to set a flag in SQL, when there is a change in value

All we need is an easy explanation of the problem, so here it is.

I have column which tells fee rates.

When there is change in value in that column, I need to set a flag for that corresponding row.

How can i check whether there is change in value from previous row and current row and flag it accordingly. There are l0’s of thousands of records.

How to set a flag in SQL, when there is a change in value

Please find below, when rate changes, then that row I have to flag.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Based on yore example I assume that by "Set a flag" you want to set a value in another column in your report.
You didn’t include a column which defines order of the rows, which is required for such query, I will use "id" column.
To get such flag you can use LAG function, which will return value in the previous row and compare it to current value:

SELECT id, rate,
CASE WHEN rate = LAG (rate) OVER (ORDER BY id) THEN 0 ELSE 1 END AS Flag
FROM ...
ORDER BY id

Method 2

It depends on what you want to do based on that flag.

  • If you only care that the row has "changed" at some point, then a flag might be enough.
  • If you care when the change happened, then a [nullable] timestamp might be more useful.
  • If you care if the change happens more than once, then you need a whole new table to record all of those changes, possibly even with "before" [and "after"] values.

In any case, the answer is the same – a Trigger.

Method 3

I think simply you can write after update Trigger on your table and update the flag for the row which value updated. Let assume you have below RATES sample table and we can update the flag using below trigger.

How to set a flag in SQL, when there is a change in value

CREATE TRIGGER [dbo].[UPDATE_FLAG] ON [dbo].RATES
AFTER UPDATE
AS
BEGIN

   DECLARE @ID  INT
   SELECT @ID = INSERTED.ID       
   FROM inserted

IF(@ID>0)
BEGIN

UPDATE RATES SET FALG=1 WHERE [email protected]
END
END

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply