Help with self-join

Transact-SQL
1

Hello,

I have a table which I need to fish out the most recent values for certain types.
Here is the table:

declare @tMain table (ID int, PartnerCode int, EndDate datetime, EventType varchar(1), EventResult varchar(1))
insert @tMain
select 1, 60, '03/09/1999', 'R', '4' union all
select 2, 60, '04/20/1999', 'C', 'D' union all
select 3, 60, '01/01/1980', 'G', 'M' union all
select 4, 60, '03/13/1981', 'R', 'M' union all
select 5, 60, '06/08/1982', 'R', 'M' union all
select 6, 73, '09/17/1995', 'C', '3' union all
select 7, 73, '05/24/2009', 'C', '2' union all
select 8, 73, '01/01/2007', 'C', '5' union all
select 9, 73, '01/01/2002', 'C', '4' union all
select 10, 99, '12/01/2018', 'R', '1' union all
select 11, 99, '12/05/2018', 'R', '0' union all
select 12, 99, '05/24/2009', 'R', 'N'

For each PartnerCode I need the most recent EndDate and EventResult for EventType='R' and the same for EventType='C'.
There are no duplicates for given PartnerCode, EndDate, EventType combination.
So I'm expecting:

PartnerCode | CDate      | CResult | RDate      | RResult
---------------------------------------------------------------------
60          | 04/20/1999 | D       | 03/09/1999 | 4
73          | 05/24/2009 | 2       | null       | null
99          | null       | null    | 12/05/2018 | 0

I came up with something like below. It seem to work, but - is there a "nicer" way to write it? This is for SQL 2012. Any ideas appreciated.

select x.PartnerCode, c1.CDate, c1.CResult, r1.RDate, r1.RResult
from (
	select distinct PartnerCode
	from @tMain
) x
left join (
	select m.PartnerCode, m.EndDate [CDate], m.EventResult [CResult]
	from @tMain m
	inner join (
		select PartnerCode, max(EndDate) EndDate
		from @tMain
		where EventType='C'
		group by PartnerCode, EventType
	) c on m.PartnerCode=c.PartnerCode and m.EndDate=c.EndDate and m.EventType='C'
) c1 on x.PartnerCode=c1.PartnerCode
left join (
	select m.PartnerCode, m.EndDate [RDate], m.EventResult [RResult]
	from @tMain m
	inner join (
		select PartnerCode, max(EndDate) EndDate
		from @tMain
		where EventType='R'
		group by PartnerCode, EventType
	) c on m.PartnerCode=c.PartnerCode and m.EndDate=c.EndDate and m.EventType='R'
) r1 on x.PartnerCode=r1.PartnerCode
where c1.CDate is not null or r1.RDate is not null
2

hi

I tried to do this

Two Rows are coming for PartnerCode = 73
how should I pick which row ???

drop create data .. 
drop table #abc 
create  table #abc  (ID int, PartnerCode int, EndDate datetime, EventType varchar(1), EventResult varchar(1))
insert into #abc 
select 1, 60, '03/09/1999', 'R', '4' union all
select 2, 60, '04/20/1999', 'C', 'D' union all
select 3, 60, '01/01/1980', 'G', 'M' union all
select 4, 60, '03/13/1981', 'R', 'M' union all
select 5, 60, '06/08/1982', 'R', 'M' union all
select 6, 73, '09/17/1995', 'C', '3' union all
select 7, 73, '05/24/2009', 'C', '2' union all
select 8, 73, '01/01/2007', 'C', '5' union all
select 9, 73, '01/01/2002', 'C', '4' union all
select 10, 99, '12/01/2018', 'R', '1' union all
select 11, 99, '12/05/2018', 'R', '0' union all
select 12, 99, '05/24/2009', 'R', 'N'

--select * from #abc
SQL ... 
; WITH cte 
     AS (SELECT partnercode, 
                CASE 
                  WHEN eventtype = 'C' THEN enddate 
                END AS CDate, 
                CASE 
                  WHEN eventtype = 'C' THEN eventresult 
                END AS CResult, 
                CASE 
                  WHEN eventtype = 'R' THEN enddate 
                END AS Rdate, 
                CASE 
                  WHEN eventtype = 'R' THEN eventresult 
                END AS RResult 
         FROM   #abc), 
     cte1 
     AS (SELECT partnercode, 
                Max(cdate) AS mcdate, 
                Max(rdate) AS maxrdate 
         FROM   cte 
         GROUP  BY partnercode) 
SELECT a.partnercode, 
       a.mcdate, 
       b.eventresult, 
       a.maxrdate, 
       e.eventresult 
FROM   cte1 a 
       LEFT JOIN #abc b 
              ON a.mcdate = b.enddate 
       LEFT JOIN #abc e 
              ON a.maxrdate = e.enddate
Result

image