Issue with case when in MS SQL

Question

I have a table with member number, name, full_desc and activity_type as below

MEM_NO  Name    full_desc                 Activity_type
1233    John    LM Application received     9097
1233    John    Retired                     9630
1234    Mark    LM Application received     9097
3325    Steve   LM Application received     9097
3325    Steve   Retired                     9630
6566    Gareth  Retired                     9630
7088    Daniel  Resign                      9629

I want output to be

MEM_NO  Name    full_desc           Activity_type     Status
1233    John    LM Application received 9097       LM Application received
1234    Mark    LM Application received 9097       LM Application received
3325    Steve   LM Application received 9097       LM Application received
6566    Gareth  Retired                 9630        No LM Recevied
7088    Daniel  Resign                  9629        No LM Recevied

Note:-

1)I want to show distinct members which has activity_type 9097 and 9630 or 9629 attached should have status as "LM Application received"

2) Member who has only activity_type 9097 attached should also so a status ' LM Application received'

3) Members who doesn't have activity_type 9097 attached but they have either 9630 or 9326 then show their status as No LM Received.

SELECT MBR.MEMBERSHIP_NO,IND.TITLE,IND.FORENAMES,IND.DATE_OF_BIRTH,DATEADD(dd, 0, DATEDIFF(dd, 0, AL.ACTION_DATE)) as 'Date',LU1.FULL_DESC,COUNT(mbr.MEMBERSHIP_NO) as Total_Retired, null as Life_app_received,null as Total_Resign,
CASE WHEN AL.ACTIVITY_TYPE in (9630,9097) THEN  'LM Application received' ELSE 'NO LIFE' END AS STATUS
 ---AL.ACTIVITY_DATE,
FROM INDIVIDUAL IND
JOIN ACTIVITY_LOG AL ON AL.INDIVIDUAL_REF=IND.INDIVIDUAL_REF
JOIN LOOKUP_TABLE LU1 ON LU1.LOOKUP_TABLE_REF=AL.ACTIVITY_TYPE-----------------------MEM - 0 - Future Cancellation - Retired
JOIN MEMBER MBR ON MBR.INDIVIDUAL_REF=IND.INDIVIDUAL_REF
WHERE  AL.ACTIVITY_TYPE IN (9630,9097) AND  AL.ACTION_DATE BETWEEN '2017-01-01' AND '2020-12-31' --AND MBR.MEMBER_STATUS=33
GROUP BY MBR.MEMBERSHIP_NO,IND.TITLE,IND.FORENAMES,IND.DATE_OF_BIRTH,DATEADD(dd, 0, DATEDIFF(dd, 0, AL.ACTION_DATE)),LU1.FULL_DESC,AL.ACTIVITY_TYPE

Above is my query which fail to bring what i want. Any help much appriciated

Answer 1

Please Check Below Query and Try to Understand. I am using it with Temporary Table you can Replace it with your Original Table name.

IF OBJECT_ID('tempdb..#Tempdata') IS NOT NULL
    DROP TABLE #Tempdata

create table #Tempdata
(
    Mem_no int,
    Name varchar(250),
    full_desc varchar(max),
    activity_type int
)

INSERT INTO #Tempdata
select '1233','John','LM Application received','9097' UNION
select '1233','John','Retired','9630'  UNION
select '1234','Mark','LM Application received','9097'   UNION
select '3325','Steve','LM Application received','9097' UNION
select '3325','Steve','Retired','9630' UNION
select '6566','Gareth','Retired','9630' UNION
select '7088','Daniel','Resign','9629'

select *, CASE WHEN activity_type = '9097' THEN 'LM Application received' ELSE 'No LM Recevied' END  as Status from (
select ROW_NUMBER() OVER(PARTITION BY Mem_no,Name ORDER BY Mem_no,Name) as Rnk,
Mem_no , Name ,full_desc ,activity_type  from #Tempdata
) as D
where D.Rnk = 1

Answer 2

Try with row_number()

select *,case when full_desc='LM Application received' then 'LM Application received' else 'No LM Recevied' end as status from 
(select mem_no,name,full_desc,activity_type,row_number() over(partition by mem_no,name order by case when full_desc='LM Application received' then 2 when full_desc='Retired' then 1
when full_desc='Resign' then 0 end desc) as rn
from tablename) a where rn=1

Answer 3

This suggests me row_number() function with conditional ordering :

select top (1) with ties t.*, (case when Activity_type =  9097 
                                    then t.full_desc else 'No LM Received' 
                               end) as Status
from table t
order by row_number() over (partition by mem_no 
                                order by (case when Activity_type =  9097 
                                               then 0 else 1 
                                          end)
                           );