Help with a Query to select NULL values before last Non NULL value

Question

Hi there,

Trying to build this query that will select NULL values before last non NULL value from highest to lowest.

for example;

ID Activity Date
1 Activity1 2022-01-01
2 Activity2 2022-01-02
3 Activity3 NULL
4 Activity4 2022-02-02
5 Activity5 NULL
6 Activity6 2022-03-01
7 Activity7 NULL
8 Activity8 NULL
9 Activity9 NULL
10 Activity10 NULL

So from the data sample above, Activity6 being the last non null value going from highest to lowest, I need to select the NULL values which are Activity5 and Activity3.

Thanks very much for help in advance.
M

Answer 1

Since you need the activities "before" an activity with a non-null Date field, it's hard to order the records. If you can rely on the id field having the same ordering as the Date field, then you could do something like this,

   SELECT * FROM dbo.activities WHERE Date IS NULL AND id < (SELECT MAX(id) FROM dbo.activities WHERE Date IS NOT NULL);  

Answer 2

Hi @Manuel Canas
How about using FIRST_VALUE function to get the last Non NULL value, check this:

;WITH CTE AS  
(  
SELECT *,FIRST_VALUE (ID)OVER(ORDER BY Date DESC) AS Last_non_NULL_Value  
FROM TableName  
)  
SELECT ID,Activity,Date FROM CTE  
WHERE Date IS NULL AND ID<Last_non_NULL_Value  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.