I currently have a table that looks like this:

I want to be able to look at the date when the Department changes(from Sales to Marketing or IT to Sales in this example) along with the previous department using the ClaimID as a key. This would be an example of what I want to return:

My initial idea is to create a subquery where I order by the ClaimID and then compare the values in the Department column with the previous row. However, I can't wrap my mind around how to do the comparison using SQL. Any help is appreciated!

CodePudding user response：

In MySQL 8 you can use the lag() window function and a CASE expression.

SELECT claimid,
       department,
       date,
       CASE
         WHEN lag(department) OVER w <> department THEN
           date
       END datechanged,
       CASE
         WHEN lag(department) OVER w <> department THEN
           lag(department) OVER w
       END previous
       FROM elbat
       WINDOW w AS (PARTITION BY claimid
                    ORDER BY date)
       ORDER BY claimid,
                date;