Search code examples
sqlsql-serverazure-sql-database

SQL Return first date prior to nulls in a list

Here is my data

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

SubscriptionPhase SubscriptionId Date PreviousDate
Utilization 1 2/23/2022 NULL
NULL 1 2/24/2022 2/23/2022
NULL 1 2/25/2022 2/23/2022
NULL 1 2/26/2022 2/23/2022
NULL 1 2/27/2022 2/23/2022
Utilization 1 2/28/2022 NULL
NULL 1 3/1/2022 2/28/2022
NULL 1 3/2/2022 2/28/2022
NULL 1 3/3/2022 2/28/2022
NULL 1 3/4/2022 2/28/2022
NULL 1 3/5/2022 2/28/2022
Utilization 1 3/6/2022 NULL

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.