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.
2条答案
按热度按时间tyg4sfes1#
You can use the window function to get the maximum date value:
CASE WHEN SubscriptionPhase IS NULL
- this is so date is only calculated for null rowsMAX(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, whilerows BETWEEN unbounded preceding AND 1 preceding
makes sure your "window" is previous rows.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.