Create end date based on max date within partition?

I have a database table labeled "Transactions," and it lacks an end date. I need to create an end date for date diff formulas in tableau.
DB table name = [pursuant].[dbo].[transactions]

Each record = a landlord with a status column and date that status started. Example: row 1 has landownerA with "Passed to Agent" status on 1/1/24. row 2 has landownerA with "Outreach" status on 1/22/24.

I need to create and end date based on [Date] column in table.

I want to turn this table into this:

Here is the code I tried using, but it’s erroring out:

  select Landowner, Status, min(Date), max(Date)
from (select Transactions.*, row_number() over (partition by Landowner, status order by 
Date) as seqnum
  from Transactions t
 ) t
group by Landowner, Status, dateadd(day, - seqnum, Date)
order by Landowner, min(Date);

>Solution :

You can use a windowed LEAD() function to look ahead to the next start date for a given landowner, DATEADD() to subtract a day, and ISNULL() to handle to "no value" case.

Something like:

SELECT
    T.LandOwner,
    T.Comments,
    T.Status,
    T.Agent,
    T.Date as StartDate,
    ISNULL(
        DATEADD(day, -1,
            LEAD(T.Date) OVER(PARTITION BY T.LandOwner ORDER BY T.Date)
            ), 
        CONVERT(DATE, GETDATE())
    ) AS EndDate
FROM pursuant.dbo.Transactions T

Results:

See this dv<>fiddle for a demo.