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 may be able to accomplish it with something like this. Though this isn't bulletproof, you could get duplicates if the closest date is tied.

```
create table Dates (from_effdt datetime, to_effdt datetime, flag varchar(1))

insert Dates (from_effdt, to_effdt, flag)
values
('2019-03-16', null, '') ,
('2018-06-14', '2019-03-16', '') ,
('2018-05-14', '2018-06-14', '') ,
('2018-01-01', '2018-05-14', '') 

select * from Dates

UPDATE Dates
SET flag = 
CASE 
WHEN from_effdt = (
    select top 1 from_effdt
    from Dates
    order by ABS ( DATEDIFF(day, from_effdt, getdate()) )
)
THEN 'Y'
ELSE
    'N'
END

```

\*update, not sure why I created it as an update. This select should do.

```
SELECT from_effdt, to_effdt,
CASE 
WHEN from_effdt = (
    select top 1 from_effdt
    from Dates
    order by ABS ( DATEDIFF(day, from_effdt, getdate()) )
)
THEN 'Y'
ELSE
    'N'
END [numberOfDaysAway]
FROM Dates

```