Как реализовать следующую задачу с использованием SQL в SQL Server

Question

Я получил эту задачу для работы, в которой я должен выбрать care_team_member_name от DIM_CARE_TEAM_MEMBER Таблица. Условия для конкретного care_team_member_Engagement_idнам нужно выбрать активного члена, т.е. care_team_member_end_date является нулевым, и для неактивных членов мы должны выбрать максимальное care_team_member_end_date для этого конкретного care_team_member_Engagement_id,

Мне удалось реализовать вторую часть с помощью следующего запроса

select  
    [care_team_member_name], 
    [care_team_member_Engagement_id],
    [care_team_member_end_date],
    B.A 
from 
    (select 
         care_team_member_name,care_team_member_end_date,
         [care_team_member_Engagement_id],
         row_number() over(partition by [care_team_member_Engagement_id] order by care_team_member_end_date) A
     from 
         DIM_CARE_TEAM_MEMBER) B  
where 
    B.A = 1

Но я не могу реализовать первую часть, где я должен выбрать care_team_member_name для определенного идентификатора обязательства

Например:

care_team_member_name   care_team_member_Engagement_id  care_team_member_start_date care_team_member_end_date
TM-000022181            a1Y0q0000000woaEAA               2017-08-16                  NULL
TM-000022182            a1Y0q0000000wobEAA               2017-08-16                  NULL
TM-000022183            a1Y0q0000000wocEAA               2017-08-16                  NULL
TM-000022184            a1Y0q0000000wodEAA               2017-08-16                  NULL
TM-000022185            a1Y0q0000000woeEAA               2017-08-16                  NULL
TM-000030523            a1Y0q0000000woVEAQ               2018-01-03                  2018-02-28
TM-000031508            a1Y0q0000000woVEAQ               2018-01-25                  2018-02-28
TM-000031798            a1Y0q0000000woVEAQ               2018-03-01                  2018-03-05
TM-000031802            a1Y0q0000000woVEAQ               2018-03-01                  2018-03-05
TM-000031803            a1Y0q0000000woVEAQ               2018-03-01                  2018-03-05
TM-000031805            a1Y0q0000000woVEAQ               2018-03-01                  2018-03-05
TM-000031806            a1Y0q0000000woVEAQ               2018-03-01                  2018-03-05
TM-000023500            a1Y0q0000000woVEAQ               2017-09-21                  2018-03-05
TM-000023503            a1Y0q0000000woVEAQ               2017-09-22                  2018-03-05

Из этой таблицы я должен выбрать имя, дата окончания которого равна нулю

Answer 1

Я не уверен, что правильно понял вопрос, но попробуйте это:

SELECT tm.*
FROM (
    SELECT care_team_member_Engagement_id id, 
         NULLIF(MAX(coalesce(care_team_member_end_date,'29991231')),'29991231') end_date 
    FROM DIM_CARE_TEAM_MEMBER
    GROUP BY care_team_member_Engagement_id 
) ids
INNER JOIN DIM_CARE_TEAM_MEMBER tm ON
        tm.care_team_member_Engagement_id = ids.id 
    AND tm.care_team_member_end_date = ids.end_date