Find rows where is null applicant_id in MSSQL-CodePudding

I need to find null applicant_id by web_user_id after the min(date) where applicant_id was first filled

For example, for web_user_id 23 we will find null applicant_id after the row_id = 3, because it is first filled applicant_id with min(date)

For web_user_id 90 we will find null applicant_id after row_id = 11, because it is first filled applicant_id with min(date)

https://prnt.sc/264ofhg

the table is:

| row_id  | applicant_id  | web_user_id  | date  |
| ------- | ------------- | ------------- | ---- |
| 1       | null          | 23            | 2020 |
| 2       | null          | 23            | 2021 |
| 3       | 77            | 23            | 2022 |
| 4       | 77            | 23            | 2023 |
| 5       | 77            | 23            | 2024 |
| 6       | null          | 23            | 2025 |
| 7       | 77            | 23            | 2026 |
| 8       | null          | 23            | 2027 |
| 9       | 77            | 23            | 2028 |
| 10      | null          | 90            | 2020 |
| 11      | 55            | 90            | 2021 |
| 12      | 55            | 90            | 2022 |
| 13      | 55            | 90            | 2023 |
| 14      | 55            | 90            | 2024 |
| 15      | null          | 90            | 2025 |
| 16      | 55            | 90            | 2026 |
| 17      | 55            | 90            | 2027 |

the condition is: select min(date), applicant_id, row_id, web_user_id and after this date I need to find rows where applicant_id is null

As a result I would to have this table: https://prnt.sc/264om6u

| row_id  | applicant_id  | web_user_id   | date |
| ------- | ------------- | ------------- | ---- |
| 6       | null          | 23            | 2025 |
| 8       | null          | 23            | 2027 |
| 15      | null          | 90            | 2025 |

SQL for creating the table

create table dbo.tabl (
row_id int,
applicant_id int,
web_user_id int,
"date" int
);

insert into dbo.tabl values 
(1, null, 23, 2020),
(2, null, 23, 2021),
(3, 77, 23, 2022),
(4, 77, 23, 2023),
(5, 77, 23, 2024),
(6, null, 23, 2025),
(7, 77, 23, 2026),
(8, null, 23, 2027),
(9, 77, 23, 2028),
(10, null, 90, 2020),
(11, 55, 90, 2021),
(12, 55, 90, 2022),
(13, 55, 90, 2023),
(14, 55, 90, 2024),
(15, null, 90, 2025),
(16, 55, 90, 2026),
(17, 55, 90, 2027);

CodePudding user response：

This is what I tried and a simple query too. You can try this one too

   Select  applicant_id, row_id, web_user_id,min("date")
     from tabl 
     where applicant_id is null
     and "date" > 2024
     group by applicant_id, row_id, web_user_id,"date";

CodePudding user response：

**This request was written in a rush. But I think it will help you.**

Select *
from (Select *
      from (Select t.*,
                   LAG(applicant_id) OVER(PARTITION BY WEB_USER_ID ORDER BY 
t."date") LAG_,
                   ROW_NUMBER() OVER(PARTITION BY t.WEB_USER_ID Order BY 
t."date") RN
              from tabl_test t)
     where not (applicant_id is null and lag_ is null))
where applicant_id is null