Using LAG to update current row and next row until null with previous row value-CodePudding

I am trying to solve an issue with LAG. I want to update the current row (if null) with the previous value and keep it repeating until null.

Here is my code:

SELECT PersonID, FirstDateofTermYear,

case 
when PhysicalOVRARiskRating is null then LAG (PhysicalOVRARiskRating, 2, PhysicalOVRARiskRating) OVER (PARTITION BY PersonID ORDER BY FirstDateofTermYear)
ELSE PhysicalOVRARiskRating END AS PhysicalOVRARiskRating,

case 
when  PsychologicalOVRARiskRating is null then  LAG (PsychologicalOVRARiskRating, 2, PsychologicalOVRARiskRating) OVER (PARTITION BY PersonID ORDER BY FirstDateofTermYear) 
ELSE PsychologicalOVRARiskRating  END AS PsychologicalOVRARiskRating

FROM [BI].[vw_Fact_OVT_CCI_V3]

WHERE  PersonID = '0258077'

ORDER BY FirstDateofTermYear;

Original result -

PersonID    FirstDateofTermYear PhysicalOVRARiskRating  PsychologicalOVRARiskRating
0258077 2020-02-03  NULL    NULL
0258077 2020-04-28  NULL    MEDIUM
0258077 2020-07-20  NULL    NULL
0258077 2020-10-12  NULL    NULL
0258077 2021-02-01  NULL    NULL
0258077 2021-04-19  NULL    NULL
0258077 2021-07-12  NULL    NULL
0258077 2021-10-05  NULL    NULL
0258077 2022-01-31  NULL    NULL
0258077 2022-04-26  NULL    LOW
0258077 2022-07-18  NULL    NULL

Current result -

PersonID    FirstDateofTermYear PhysicalOVRARiskRating  PsychologicalOVRARiskRating
0258077 2020-02-03  NULL    NULL
0258077 2020-04-28  NULL    MEDIUM
0258077 2020-07-20  NULL    MEDIUM
0258077 2020-10-12  NULL    MEDIUM
0258077 2021-02-01  NULL    NULL
0258077 2021-04-19  NULL    NULL
0258077 2021-07-12  NULL    NULL
0258077 2021-10-05  NULL    NULL
0258077 2022-01-31  NULL    NULL
0258077 2022-04-26  NULL    LOW
0258077 2022-07-18  NULL    LOW

Expected result -

PersonID    FirstDateofTermYear PhysicalOVRARiskRating  PsychologicalOVRARiskRating
258077  03/02/2020  NULL    NULL
258077  28/04/2020  NULL    MEDIUM
258077  20/07/2020  NULL    MEDIUM
258077  12/10/2020  NULL    MEDIUM
258077  01/02/2021  NULL    MEDIUM
258077  19/04/2021  NULL    MEDIUM
258077  12/07/2021  NULL    MEDIUM
258077  05/10/2021  NULL    MEDIUM
258077  31/01/2022  NULL    MEDIUM
258077  26/04/2022  NULL    LOW
258077  18/07/2022  NULL    LOW

How do I repeat the value until we find a value other than null? LAG function is only repeating once or twice, not every time.

CodePudding user response：

As per @lptr's comment

select PersonID, FirstDateofTermYear
    , min(PhysicalOVRARiskRating) over (partition by PersonID, grpphy)
    , min(PsychologicalOVRARiskRating) over (partition by PersonID, grppsy)
from (
    select *
        , count(PhysicalOVRARiskRating) over (partition by PersonID order by FirstDateofTermYear rows unbounded preceding) as grpphy
        , count(PsychologicalOVRARiskRating) over (partition by PersonID order by FirstDateofTermYear rows unbounded preceding) as grppsy
    from [BI].[vw_Fact_OVT_CCI_V3]
) as d