Please help with SQL Server query. I need to ignore data when some values contain same value or no change in the values from dates a to dates b, and only show data that have different values. For example if I have the following table:

So I have data that has dates between 20230101 - 20230110

The output should be as follow:

Its remove pers and incl. because there is no change in their values.

Please help, Thanks in advance.

Already doing some query from this forum with min max but there is no result cause I'm confused with that before after values

CodePudding user response：

Find the sum of 'before' and 'after' for each id, then select rows where the difference between two sums is not equal to 0.

WITH CTE AS
(
  SELECT *, 
    SUM(before) OVER (PARTITION BY id) before_sum,
    SUM(after) OVER (PARTITION BY id) after_sum
  FROM yourTbl
)
SELECT id, date_, before, after
FROM CTE
WHERE before_sum - after_sum <> 0
ORDER BY ID

Demo

This can be simplified to SUM(before - after) OVER (PARTITION BY id) flag ... where flag <> 0.

CodePudding user response：

select id, sum(before) as b, sum(after) as a
from t1
group by id
having (b - a) <> 0;