SELECT c.casecode          AS 'Casecode',
       c.name              AS 'Case Name',
       ct.LoginName        AS 'Case Administrator',
       mee.meetingpurpose  AS 'Meeting Purpose'
FROM   ips_case c
       INNER JOIN ips_contact co
            ON  c.casecode = co.casecode
       INNER JOIN ips_casestaff ct
            ON  ct.CaseCode = co.CaseCode
                -- left join ips_meeting mee ON mee.CaseCode = c.CaseCode
       OUTER APPLY
		(
			SELECT TOP(1) mee.meetingpurpose
			FROM   ips_meeting mee
			WHERE mee.casecode = c.casecode
			ORDER BY meetingdate DESC
		) AS mee
WHERE  co.name LIKE '%Council%'
       AND ct.Role = 30
       AND ct.nomineetype = 1

select 
c.casecode AS 'Casecode', 
c.name AS 'Case Name', 
--co.name AS 'Creditor', 
--co.cref AS 'Reference',
ct.[Case Administrator],
ct.[Trustees Name],
 mee.meetingpurpose, 
c.appdate AS 'Date of appointment',
[Progress Report],
[Assets & Value],
[Estimated Time of Dividend]
from 
ips_case c 
inner join ips_contact co ON c.casecode = co.casecode
inner join (select CaseCode,
				   max(case when Role = 30 and nomineetype = 1 then LoginName end) as [Case Administrator],
				   max(case when Role = 10 and nomineetype = 1 then nominee end) as [Trustees Name],
            from ips_casestaff 
			group by CaseCode
		   )ct 
ON ct.CaseCode = c.CaseCode
inner join (select CaseCode,
				   max(case when ntype = 'COUNCIL PROGRESS' then shortnote end) as [Progress Report],
				   max(case when ntype = 'BKYPROP' then shortnote end) as [Assets & Value],
				   max(case when ntype = 'COUNCIL DISTRIBUTION' then shortnote end) as [Estimated Time of Dividend],
			from   (select *,row_number() over (partition by casecode order by moddatetime desc) as seq from ips_notes )t
			where seq=1
			group by CaseCode
			)n
on n.casecode = c.casecode
 left join (select row_number() over (partition by CaseCode order by meetingdate desc) as seq, CaseCode,meetingpurpose
            from ips_meeting 
			)mee 
ON mee.CaseCode = c.CaseCode 
and seq=1
where c.casecode = 'W02724' and co.name like '%Council%'