CODEWITHSUNDEEP
sqlsql-server
Muhammad Akmal
Muhammad Akmal

Reputation: 59

How to return result from SQL Query when any changes in status column?

I am still new in writing complex query. It will be helpful if anyone could give me any solution to this.

I have set of records, which I would it to return the Date and Status when the status changes.

https://i.sstatic.net/xLUga.png

Thanks in advance.

Upvotes: 1

Views: 752

Answers (1)

SqlZim
SqlZim

Reputation: 38023

In SQL Server 2012+, using lag():

select s.[date],s.[status]
from (
  select [date],[status],lag([status]) over (order by date) as prev_status
  from tbl
  ) s
where s.[status]<>s.prev_status 
  or s.prev_status is null

Prior to SQL Server 2012+, using outer apply():

select t.[date],t.[status]
from tbl as t
outer apply (
  select top 1 i.[status] as prev_status
  from tbl as i
  where i.[date] < t.[date]
  order by i.[date] desc
  ) s
where t.[status]<>s.prev_status 
  or s.prev_status is null

Upvotes: 3

Related Questions