T-SQL : how to select row with multiple conditions

Question

I have below data set in SQL Server and I need to select the data with conditions in order below:

First, check to see if date_end is 1/1/2099, then select the row that has smallest days gap and skill_group is not SWAT for rows have same employee_id, in this case that is row 2.

Second, for rows that do not have 1/1/2099 date_end, select row that has most recent day date_end, in this case it's row 4.

ID  employee_id last_name   first_name  date_start  date_end    skill_group
---------------------------------------------------------------------------
1   N05E0F  Mike    Pamela  12/19/2013  1/1/2099    SWAT
2   N05E0F  Mike    Pamela  9/16/2015   1/1/2099    Welcome Team
3   NSH8A   David   Smith   12/19/2013  9/16/2016   Unlicensed
4   NSH8A   David   Smith   8/16/2015   10/16/2016  CMT

SqlZim · Accepted Answer · 2017-01-20 20:57:59Z

1

There are many ways to do this. Here are some of them:

top with ties version:

select top 1 with ties
    *
  from tbl
  where skill_group != 'SWAT'
  order by 
    row_number() over (
      partition by employee_id
      order by date_end desc, datediff(day,date_start,date_end) asc 
      )

with common_table_expression as () using row_number() version:

with cte as (
  select  *
      , rn = row_number() over (
              partition by employee_id
              order by date_end desc, datediff(day,date_start,date_end) asc 
            )

    from tbl
    where skill_group != 'SWAT'
)
select *
  from cte
  where rn = 1

answered Jan 20, 2017 at 20:57

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mixer Over a year ago

Thank you for your help.

Collectives™ on Stack Overflow

T-SQL : how to select row with multiple conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related