I have a regular table in the SQL Server database as the below
| Name | Status | Modified Date |
|---|---|---|
| X | Fail | 16/09/2021 |
| X | Fail | 28/09/2021 |
| X | Done | 02/10/2021 |
| Y | Fail | 30/09/2021 |
| Y | Done | 02/10/2021 |
And I'm looking to make a report as a historical report based on the group of data as the below result
| Name | Current Status | Previous status | Modified Date |
|---|---|---|---|
| X | Done | Fail | 02/10/2021 |
| X | Fail | Fail | 28/09/2021 |
| Y | Done | Fail | 02/10/2021 |
any help will be highly appreciated
CodePudding user response：
select * from (
select * , LAG(Status) over (partition by Name order by ModifiedDate) prvstatus
from table
) t where t.prvstatus is not null;