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) 

```
You can simply do this:

```
CASE 
WHEN from_effdt = (
    select from_effdt
      from Dates
      where abs(datediff(second, from_effdt, getdate()))
        = (select min(
        abs(datediff(second, from_effdt, getdate()))
)
from Dates)
)
THEN 'Y'
ELSE
    'N'
END

```