Gaps and Island problem - SQL Server - Snapshot model

Question

I am having source data like this:

Scenario 1:

as_of_date	issue_id	issue_status_name
9/1/2020	123456	Pending Validation
9/2/2020	123456	Pending Validation
9/3/2020	123456	Closed
9/4/2020	123456	Closed
9/5/2020	123456	Closed
9/6/2020	123456
9/7/2020	123456	Closed
9/8/2020	123456	Closed
9/9/2020	123456	Closed
9/10/2020	123456	Closed

this query is giving correct values:

Basically when status in pending validation or closed

https://dbfiddle.uk/YRJPI8DW

(select
as_of_date,
issue_id,
issue_status_name,
row_number() over (partition by issue_id order by as_of_date) - row_number() over (partition by issue_id, output_flag order by as_of_date) as grp
,output_flag
from issues cross apply
(values (case when issue_status_name in ('Pending Validation', 'Closed')
then 1 else 0 end)) v(output_flag)
)
select
as_of_date,
issue_id,
issue_status_name,
case when output_flag = 1 then min(as_of_date) over (partition by issue_id, grp) end as desired_output
from cte
order by as_of_date```

Scenario 2:

when previous status = pending sustaibility and current status in (validation or closed) we get that records asofdt and carry forward

we set null when reverse happens like desired output

as_of_date	issue_id	issue_status_name	Prev_status_name	Desired_Output
9/1/2020	456789	Pending Validation	NULL	NULL
9/2/2020	456789	Pending Validation	Pending Validation	NULL
9/3/2020	456789	Closed	Pending Validation	NULL
9/4/2020	456789	Closed	Closed	NULL
9/5/2020	456789	Closed	Closed	NULL
9/6/2020	456789	Pending Sustainability	Closed	NULL
9/7/2020	456789	Closed	Pending Sustainability	9/7/2020
9/8/2020	456789	Closed	Closed	9/7/2020
9/9/2020	456789	Closed	Closed	9/7/2020
9/10/2020	456789	Closed	Closed	9/7/2020

I tried this query but it gives completely wrong output: https://dbfiddle.uk/NskgiHL3

(select
as_of_date,
issue_id,
issue_status_name,
row_number() over (partition by issue_id order by as_of_date) - row_number() over (partition by issue_id, output_flag order by as_of_date) as grp
,output_flag
  from issues cross apply
(values (case when issue_status_name in ('Pending Validation', 'Closed') and prev_status_name = 'Pending Sustainability'
then 1 else 0 end)) v(output_flag)
)
select
as_of_date,
issue_id,
issue_status_name,
case when output_flag = 1 then min(as_of_date) over (partition by issue_id, grp) end as desired_output
from cte

Answer 1

1. Fixed your date format (but it's better to insert it properly in the first place) - fixed_date

2. I found the latest row which changed to Close, for each issue_id - latest_close

3. If the latest_close date is past (or equal) to current - show it - case

with cte as
(
    select  *
    from    issues
    outer apply
    (
        select convert(date,as_of_date, 101) as fixed_date
    )   as fix
)

select      curr.*,
            case
                when latest_close.fixed_date <= curr.fixed_date
                then latest_close.fixed_date
            end     as res

from        cte     as curr

outer apply
(
    select  top(1)
            latest_close.*
    from    cte     as  latest_close
    where   latest_close.issue_id = curr.issue_id
    and     latest_close.issue_status_name = 'Closed'
    and     latest_close.Prev_status_name <> 'Closed'
    order by latest_close.fixed_date desc
)   as      latest_close

order by    curr.issue_id, curr.fixed_date

https://dbfiddle.uk/AcXykMFV

as_of_date	issue_id	issue_status_name	Prev_status_name	Desired	fixed_date	res
9/1/2020	456789	Pending Validation	null	null	2020-09-01	null
9/2/2020	456789	Pending Validation	Pending Validation	null	2020-09-02	null
9/3/2020	456789	Closed	Pending Validation	null	2020-09-03	null
9/4/2020	456789	Closed	Closed	null	2020-09-04	null
9/5/2020	456789	Closed	Closed	null	2020-09-05	null
9/6/2020	456789	Pending Sustainability	Closed	null	2020-09-06	null
9/7/2020	456789	Closed	Pending Sustainability	9/7/2020	2020-09-07	2020-09-07
9/8/2020	456789	Closed	Closed	9/7/2020	2020-09-08	2020-09-07
9/9/2020	456789	Closed	Closed	9/7/2020	2020-09-09	2020-09-07
9/10/2020	456789	Closed	Closed	9/7/2020	2020-09-10	2020-09-07