[SOLVED] sql server lag function where condition

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

Solution

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