| Date | Name | Expected Rank | ID |
|---|---|---|---|
| Null | Consultant | 2 | 100 |
| 31-07-2024 | Advisor | 1 | 100 |
| 14-03-2024 | Trainer | 1 | 101 |
| 05-02-2024 | Trainer | 2 | 101 |
| Null | Security | 1 | 102 |
| 01-03-2024 | Security | 2 | 102 |
| 06-12-2023 | Security | 3 | 102 |
| 30-04-2025 | Developer | 2 | 103 |
| 09-07-2024 | Sr Dev | 1 | 103 |
| 29-02-2024 | Web Developer | 3 | 103 |
| 08-10-2023 | Designer | 4 | 103 |
I am looking for sql script that can give row number for future and history rows. I can’t get the right approach. Please help me out. Please find attached screenshot that I am expecting as input
Select datecol,JobName
,ROW_NUMBER() OVER (PARTITION BY (CASE WHEN isnull(convert(date,left(datecol,10)),’9999-12-31′) > getdate() then 1
WHEN isnull(convert(date,left(datecol,10)),’9999-12-31′) < getdate() then 2
else 3
end)
Order by convert(date,left(datecol,10)) desc)
from Targettable
Basically I would to achive the following
-
for expired rows, max date value need to be processed as Rownum = 1
-
for future dated rows and null value dated rows, min date value need to be processed as Rownum = 1