[SQL] sql server lag function where condition

In the database, I have +1000,+2000,+3000..... increasing values according to the previous value. These sometimes do not increase, but decrease, I wrote a listing query to find this out.

select NUMBER-lag(NUMBER) over (ORDER BY DATE_TIME) AS 'DIFF'
from exampleTable with(nolock)
WHERE CONDITION1='abcdef' AND DATE_TIME >='20220801'


This works and I export to excel and filter and find the ones less than 0, but should I add them directly to the where part in sql?

I tried HAVING because it is a non-normal field, and it didn't work either.

AND (NUMBER-lag(NUMBER) over (ORDER BY DATE_TIME))<0
ORDER BY DATE_TIME ASC

Continue reading...