How do I rank/filter these records in a desired manner

Question

Following is a sample data of the:-

emp doa
1   2014-01-01 00:00:00.000
1   2014-01-05 00:00:00.000
1   2014-01-08 00:00:00.000
1   2014-01-12 00:00:00.000
1   2014-01-15 00:00:00.000
2   2014-01-01 00:00:00.000
2   2014-01-05 00:00:00.000
2   2014-01-10 00:00:00.000
2   2014-01-12 00:00:00.000
2   2014-01-15 00:00:00.000
3   2014-01-01 00:00:00.000
3   2014-01-05 00:00:00.000
4   2014-01-10 00:00:00.000
4   2014-01-12 00:00:00.000
4   2014-01-15 00:00:00.000

doa - Date of assigning a project

The requirement is to select the records which is closest to today - giving higher preference to past dates - which would indicate current assignment.

The expected results are-

emp doa
1   2014-01-08 00:00:00.000
2   2014-01-05 00:00:00.000
3   2014-01-05 00:00:00.000
4   2014-01-10 00:00:00.000

There are a few restrictions which I have:-

• The database (table) can't be changed - normalization is out of question.
• The actual data is huge - over 6 million records - performance is must.
• The actual data has some more fields (like payment per assignment, hours worked etc.) and hence grouping can't be done either.

The database is in SQL Server 2008 R2. Looking ahead for a solution.

Answer 1

This seems like a job for row_number(). The hard part is getting the order by clause correct. I think the following encapsulates the logic you are looking for:

select ed.*
from (select ed.*,
             row_number() over (partition by emp
                                order by (case when doa <= getdate() then 0 else 1 end),
                                         abs(datediff(day, doa, getdate()))
                               ) as seqnum
      from empdoa ed
     ) ed
where seqnum = 1;