Pick Max-1 Date depending on other column

[help4qv123]

Hello Experts,

I am stuck with a tricky logic . Could you plz  help me on this.I have attached the sample QVW.

-------------Check the start date and end date with respect to the state----------

for ex: my start date is 13 , my status started with F1 but on the day end the state was 6, but the state was continuing for the next date
Hence i have to also check the end of that state (Which is 6) and that ends on 20th. And i will pick up the previous date which is 19
(Becz for the 20 th date its a fresh date and it also has some other state which comes under another record)
Then on the 20 end the state is 3, but as the state continues and ends on 25 i will pick up the 24 as my end date.

Please let me know if something is unclear.

Date:

Output: