I think you are on the right track to use a window function .

In comments you said there is always one date only per empid, all others are NULL .

So you can just use any window function that fetches this date and select it, for example MAX :

SELECT
  empid, claimid, flag,
  MAX(due_date) 
    OVER(PARTITION BY empid) AS due_date
FROM yourtable
ORDER BY empid;

Note: This is very "hard", you would also "override" dates if there are surprisingly multiple dates per empid.

If you want to explicitly override NULL values only, you can use COALESCE and set for example the latest date (using MAX ) ...

SELECT
  empid, claimid, flag,
  COALESCE(due_date, MAX(due_date) 
    OVER(PARTITION BY empid)) AS due_date
FROM yourtable
ORDER BY empid;

...or the first date that is NOT NULL (using FIRST_VALUE ).

SELECT
  empid, claimid, flag,
  COALESCE(due_date, FIRST_VALUE(due_date) IGNORE NULLS
    OVER(PARTITION BY empid ORDER BY empid)) AS due_date
FROM yourtable;

One last thing: You also mentioned in comments you want to take the date from the row where the flag is Y . One could read this comment as you want to do nothing in case the date column is NULL for flag = Y . So we can add a CASE in above queries like this:

SELECT
  empid, claimid, flag,
  COALESCE(due_date, FIRST_VALUE(
    CASE WHEN flag = 'Y' THEN due_date END) IGNORE NULLS
    OVER(PARTITION BY empid ORDER BY empid)) AS due_date
FROM yourtable;

Try out all these things on this sample fiddle , there you can see the differences between these options.

I would probably stick to a correlated query in this scenario. Because you state there is only one unique date per empid you can use top and avoid any expensive sorts and spools, the following should perform very well.

select empid, claimid, flag, (
  select top(1) due_date from @t t2
  where t2.empid = t.empid and flag = 'Y' and due_date is not null
)due_date
from @t t;