Question: Need help create a case statement to find the closest date from date table. My data: <https://imgur.com/hkBu4SA>

I basically want to set:

Y flag if it's closest to today's date from a.FROM\_EFFDT and is not null.
F if to\_effdate is null
else N

```
         WHEN a.FROM_EFFDT < GETDATE() AND (to_effdate) IS NOT NULL THEN 'Y' 
         WHEN to_effdate IS NULL THEN 'F' 
         ELSE 'N' 

```

Answer: 
You can use window functions:

```
(case when row_number() over (order by abs(datediff(day, getdate(), to_effdate)) = 1
      then 'Y'
      when to_effdate is null then 'F'
      else 'N'
 end) 

```
```
ROW_NUMBER() Over (Partition by id order by to_effdt desc)
,id
,from_effdt
,to_effdt
, CASE WHEN (ROW_NUMBER() Over (Partition by id order by to_effdt desc) = 1) THEN ('Y')
       WHEN (to_effdt IS NULL) THEN ('F') ELSE ('N') End as flag
from a

```