sql server lag function where condition

kadocolak 0
Viewed 41

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
lag sql-server sql
1 Answers

So basically it is like this,

  ;WITH CTE AS (
                    select NUMBER-lag(NUMBER) over (ORDER BY DATE_TIME) AS'DIFF' from exampleTable with(nolock) 
WHERE CONDITION1='abcdef' AND DATE_TIME >='20220801'
) 
SELECT * FROM CTE WHERE DIFF <0
Srinivasan Rajasekaran0
Related