How to implement the following task using SQL in SQL Server

Question

I got this task to work on in which I have to select the care_team_member_name from the DIM_CARE_TEAM_MEMBER table. Conditions are that for a particular care_team_member_Engagement_id, we need to select the member which is active i.e its care_team_member_end_date is null and for inactive members we have select the maximum care_team_member_end_date for that particular care_team_member_Engagement_id.

I was able to implement the second part with the following query

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

But I am unable to implement the first part where I have select care_team_member_name for particular engagement id

For example:

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

From this table, I have to select name whose end date is null

i am expecting care team member name should be TM-000023503 for engagement id a1Y0q0000000woVEAQ and TM-000022181 for engagement id a1Y0q0000000wobEAA — gaurav b, Mar 07 '18 at 06:30

score 0 · Answer 1 · answered Mar 06 '18 at 19:25

I'm not sure I understand the question correctly, but try this:

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

How to implement the following task using SQL in SQL Server

1 Answers1