SQL Server SQL Return first date prior to nulls in a list

muk1a3rh  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(121)

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.

tyg4sfes

tyg4sfes1#

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.

nmpmafwu

nmpmafwu2#

What I need to return is whats in the PreviousDate 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.

I hope I understood your requirements correctly. This is how I can see this sql query.

SELECT SubscriptionId,Date,PreviousDate
FROM (
  SELECT
    SubscriptionId,
    Date,
    SubscriptionPhase,
    LAG(Date, 1, NULL) OVER (PARTITION BY SubscriptionId ORDER BY Date) AS PreviousDate
  FROM table
) AS T
WHERE SubscriptionPhase IS NULL
ORDER BY SubscriptionId;

相关问题