Select row each time value changes based on date order

Question

Good Day Hopefully I can explain my problem well enough. I do not have any sample query as I can not get anything to work. But my problem is I have a table that contains a list of accounts and their status and a date that a change occurred on the account. I need to pull the account number and each time the status changes along with the first date it changed. I have tried using rank and min(date) and max(date); the problem I am running into is the account can go back and forth between statuses and I need each time it changes. Also, a new row could be in the table with the same account number and status but the update date is different.

This is sample data:

abc C50 1/20/2022
abc C50 1/21/2022
abc C09 2/20/2022
abc C50 3/1/2022
def A54 1/20/2022
def A26 1/21/2022
def A26 2/20/2022
def A54 3/1/2022

As you can see account abc has 3 instances of C50 and one instance of C09 for my results I would expect to see, the earliest of the first two C50s, then the C09 and then next C50, since its a new status change.

abc C50 1/20/2022
abc C09 2/20/2022
abc C50 3/1/2022

For the second account def; I would expect to see the first A54, the first A26, then the next A54 on the 1st as it is a new instance.

def A54 1/20/2022
def A26 1/21/2022
def A54 3/1/2022

Answer 1

IMO With this kind of problem I find it better to think of a different kind of model. The query you are asking for is not easy to write (if at all possible) and would be difficult to maintain moving forward if your underlying structure changes

So what options do you have?

Option 1 Create a summary table of ACTUAL changes. Think of this table of a log/audit table where you only record a change when it happens. Then you can simply read from this table and out put results

[ChangeTable]
ABC C50 1/20/2022
ABC C09 2/20/2022
ABC C50 3/1/2022

Option 2 Create a revision column on your main table. You would start with 1 for the first instance and only increase the revision if a change happens. e.g.

                    REVISION
abc C50 1/20/2022   0 First instance
abc C50 1/21/2022   -1 or null if you prefer indicates no change
abc C09 2/20/2022   1 First change
abc C50 3/1/2022    2 Second change

Then you can get all revisions for an account that is greater than ZERO and ordered by revision (or desc so newest on top). You can also use this column so you can show 1st change, 2nd change etc.

Both options you would need to check for changes before you insert and append/update where appropiate.

Now you will probably say this is data I get and I cant modify the application. If so then you may be able to write a small application that is scheduled to do this work for you on a hourly/nightly basis