SQL Return first date prior to nulls in a list

Going to get straight to the request:

Here is my data

(Thanks Jonas)
Fiddle Link: http://sqlfiddle.com/#!18/7ff39/1

What I need to return is whats in the PreviousDate column (In addition to Date column). Considering each SubscriptionId, I want to find the previous entry where SubscriptionPhase isnt NULL and return that date so I use that date for something else.

>Solution :

You can use the window function to get the maximum date value:

    SELECT  *
    ,   CASE WHEN SubscriptionPhase IS NULL 
            THEN MAX(CASE WHEN subscriptionphase IS NOT NULL THEN cast(date AS date) END) OVER(partition BY subscriptionid ORDER BY cast(date AS date) rows BETWEEN unbounded preceding AND 1 preceding)
        END AS prevDate
    FROM    (
        VALUES  (N'Utilization', 1, N'2/23/2022', NULL)
        ,   (NULL, 1, N'2/24/2022', N'2/23/2022')
        ,   (NULL, 1, N'2/25/2022', N'2/23/2022')
        ,   (NULL, 1, N'2/26/2022', N'2/23/2022')
        ,   (NULL, 1, N'2/27/2022', N'2/23/2022')
        ,   (N'Utilization', 1, N'2/28/2022', NULL)
        ,   (NULL, 1, N'3/1/2022', N'2/28/2022')
        ,   (NULL, 1, N'3/2/2022', N'2/28/2022')
        ,   (NULL, 1, N'3/3/2022', N'2/28/2022')
        ,   (NULL, 1, N'3/4/2022', N'2/28/2022')
        ,   (NULL, 1, N'3/5/2022', N'2/28/2022')
        ,   (N'Utilization', 1, N'3/6/2022', NULL)
    ) t (SubscriptionPhase,SubscriptionId,Date,PreviousDate)

CASE WHEN SubscriptionPhase IS NULL – this is so date is only calculated for null rows

MAX(CASE WHEN subscriptionphase IS NOT NULL THEN cast(date AS date) END)
this takes the maximum date where phase is not null grouped by the ID.
ORDER BY cast(date AS date) ensures correct sort, while rows BETWEEN unbounded preceding AND 1 preceding makes sure your "window" is previous rows.