SELECT		c.ChildRef,
		c.AgencyCode AS LA,
		a.Region AS [FS Region],
		c.ChildrenID,
		c.ChildsFirstName + ' ' + c.ChildsSurname AS CYP,
		c.ChildsDateOfBirth,
		c.AgeAtReferral, 
		c.ChildsEthnicOriginF AS Ethnicity,
		c.LegalStatus,
		c.TypeOfPlacement,
		CASE
			WHEN c.TypeOfPlacement LIKE 'Parent & Child' THEN 'Yes'
			ELSE 'No'
		END AS [P&C Referral],
		c.PlacedWithSiblings AS [With Sibs],
		c.SiblingGroup,
		c.NumberOfSiblings,
		c.CurrentSituation,
		f.ChildrenAction,
		f.ChildrenActionDate AS [Referral Date],
		f.ChildrenActionNote AS [Notified Notes],
		c.WithdrawnDate AS [Withdrawal Date],
		c.ReasonForWithdrawal AS [Withdrawal Reason],
		c.WithdrawalNotes AS [Withdrawal Notes]
FROM		dbo.Tbl_Children AS c
INNER JOIN	dbo.Tbll_Agents AS a ON a.AgencyCode = c.AgencyCode
OUTER APPLY	(
			SELECT TOP(1)	x.ChildrenAction,
					x.ChildrenActionDate,
					x.ChildrenActionNote
			FROM		dbo.Tbl_ChildrenProgress AS x
			INNER JOIN	dbo.Tbl_ChildrenProgress AS y ON y.ID = x.RelatedProgID
			WHERE		x.ChildrenID = c.ChildrenID
					AND x.ChildrenAction LIKE N'%notified%'
			ORDER BY	x.ChildrenActionDate DESC
		) AS f
WHERE		c.ChildrenID IS NOT NULL
ORDER BY	c.SiblingGroup,
		c.AgencyCode;