I have a list and the returned table looks like this. I took the preview of only one car but there are many more.
What I need to do now is check that the current KM value is larger then the previous and smaller then the next. If this is not the case I need to make a field called Trustworthy and should fill it with either 1 or 0 (true/ false).
The result that I have so far is this:
validKMstand and validkmstand2 are how I calculate it. It did not work in one list so that is why I separated it.
In both of my tries my code does not work.
15756 should be 0 in validkmstand2 as it is larger than the next value. 15757 should be 0 in validKMstand as it is smaller than the previous value
Here is the code that I have so far.
FullList as ( SELECT * FROM eMK_Mileage as Mileage ) , ValidChecked1 as ( SELECT UL1.*, CASE WHEN EXISTS( SELECT TOP(1)UL2.* FROM FullList AS UL2 WHERE UL2.FK_CarID = UL1.FK_CarID AND UL1.KM_Date > UL2.KM_Date AND UL1.KM > UL2.KM ORDER BY UL2.KM_Date DESC ) THEN 1 ELSE 0 END AS validkmstand FROM FullList as UL1 ) , ValidChecked2 as ( SELECT List1.*, (CASE WHEN List1.KM > ulprev.KM THEN 1 ELSE 0 END ) AS validkmstand2 FROM ValidChecked1 as List1 outer apply (SELECT TOP(1)UL3.* FROM ValidChecked1 AS UL3 WHERE UL3.FK_CarID = List1.FK_CarID AND UL3.KM_Date <= List1.KM_Date AND List1.KM > UL3.KM ORDER BY UL3.KM_Date DESC) ulprev ) SELECT * FROM ValidChecked2 order by FK_CarID, KM_Date