SELECT
			OBR.cppolref,
			OBR.ValidFrom,
			S.FSASegment,
			S.Warning,
			F.Deceased,
			F.VIP,
			F.AltAddress,
			F.Flats,
			F.HEC,
			F.SpecialNeeds,
			coalesce(F.SpecialNeedsDescription,'') as 'SpecialNeedsDescription',
			PF.additionalElementPrice,
			coalesce(count(CDC.complaintref),0) + coalesce(count(CMS.complaintref),0) as 'TotComplaints',
			coalesce(count(CDC2.complaintref),0) + coalesce(count(CMS2.complaintref),0) as 'TotOpenComplaints',
			coalesce(count(CDC3.complaintref),0) + coalesce(count(CMS3.complaintref),0) as 'TotOpenFSAComplaints'
FROM
			opsBatchRecord OBR
	join
			Segments S on S.cppolref = OBR.cppolref
			AND S.validfrom = OBR.Validfrom
	join
			cpfact F on F.cppolref = OBR.cppolref
			AND F.validFrom = OBR.ValidFrom
	join
			priceFact PF on PF.cppolref = OBR.cppolref
			AND PF.ValidFrom = OBR.ValidFrom
	left join
			CDCComplaints CDC on CDC.cppolref = OBR.cppolref
			AND CDC.ValidFrom = (SELECT Max(ValidFrom) from CDCComplaints Where Validto <= f.validto)
	left join
			CMSComplaints CMS on CMS.cppolref = OBR.cppolref
	left join
			CDCComplaints CDC2 on CDC2.cppolref = OBR.cppolref
			AND CDC2.ValidFrom = (SELECT Max(ValidFrom) from CDCComplaints Where Validto <= f.validto)
			AND CDC2.Status = 'Open'
	left join
			CMSComplaints CMS2 on CMS2.cppolref = OBR.cppolref
			AND CMS2.status in ('open','FSA open') 
	
	left join
			CDCComplaints CDC3 on CDC3.cppolref = OBR.cppolref
			AND CDC3.ValidFrom = (SELECT Max(ValidFrom) from CDCComplaints Where Validto <= f.validto)
			AND CDC3.Status = 'Open'
			AND CDC3.FSAReportable = 1
	left join
			CMSComplaints CMS3 on CMS3.cppolref = OBR.cppolref
			AND CMS3.status = 'FSA open'
WHERE
			OBR.BatchID = 1001
Group by
			OBR.cppolref,
			OBR.ValidFrom,
			S.FSASegment,
			S.Warning,
			F.Deceased,
			F.VIP,
			F.AltAddress,
			F.Flats,
			F.HEC,
			F.SpecialNeeds,
			F.SpecialNeedsDescription,
			PF.additionalElementPrice