I have two columns where in the first row (inspection), there are some violations that are spotted and noted… can be 15+ different violations.

Then on every subsequent inspection, there can be some violations that are completed (those that are resolved can NOT show again as unresolved). Number can vary and can even be that inspection didn’t see any of remaining violations resolved so for that one it would be an empty row but still has to be counted.

What I need as a result are unresolved violations after each inspection …

I need this in T-SQL. I know how to use LAG, LEAD, FIRST_VALUE etc. Saying that as I tried to resolve it via window funcs. I have created func to get difference between the values (like violations remaining – violations in inspection) but really what I can’t figure is how to move past 2nd inspection

Those are aggregates of the tables beneath and if you have some kind of advice I can go and change queries that bring data in this format. Only thing is that for an inspection I do not have recorded answers for each of violations spotted on first but only one that is cleared/resolved

Thanks in advance