-2

I have a query that spits out the following result set

Query Result

Basically it is a result set of which state an employee was in on a certain date and what their status was. What I need is a way to get the rows where the employee switched states or changed from Inactive to Active. I have highlighted the rows I need in the picture. I can get the results using complex joins but I wanted to see if this was possible using some partition function.

Any help is greatly appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
From The Bayou
  • 1
  • 1
    You might look into LAG. https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15 – Sean Lange Nov 17 '21 at 19:47
  • 1
    Like `CASE WHEN LAG(STATE) OVER (PARTITION BY ORDER BY EFFDT) <> STATE THEN 'State was changed' END AS state_change` (as an example). – JNevill Nov 17 '21 at 19:49
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Nov 17 '21 at 20:21
  • Does this answer your question? [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) – Charlieface Nov 18 '21 at 09:58
  • Please provide enough code so others can better understand or reproduce the problem. – Community Nov 19 '21 at 17:33

1 Answers1

0

use the window lag to get the a prior row column value and order by date

with cte
as
(
    select
    date,
    value,
    coalesce(
    lag(value) over (order by date asc),
    1) as last_value
    from my_table
    order by date asc
)

select * from cte where value <> last_value
Golden Lion
  • 3,840
  • 2
  • 26
  • 35