sample.sql

select distinct dd.AccountCode,dd.AccountID,dd.BalanceCurrent,dd.Phone
			,fm.Id,fm.sMAC,fm.sSerialNumber,fm.EQUIPMENTTYPE_Name,fm.ADDRESS_Zip,fm.iItemID,ServiceStatus,fm.iVideoSystemID
			from
			(select distinct dd.AccountCode,dd.AccountID,dd.BalanceCurrent,coalesce(pp.phone,dd.Phone) as Phone,ServiceStatus from
			(select distinct ar.AccountCode,ca.AccountID,ar.BalanceCurrent,
			coalesce(
			nc.PHONE,nc.AlternatePhone) as Phone,sv.ServiceStatus from
			[OMNIA_ESUM_P_SUMd_AR].[dbo].[ArAccount] ar
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[CusAccount]ca on ar.AccountCode=ca.AccountCode
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[SrvService]sv on ca.AccountID=sv.AccountID
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[Srvitem] si on sv.serviceid=si.ServiceID
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[CusAccountContact] cac on ca.AccountID=cac.AccountID
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[NamContact] nc on cac.ContactID=nc.ContactID)dd
				left join
			(SELECT distinct ph.PHONE,ph.ItemID,ca.AccountCode
			FROM [OMNIA_ESUM_P_SUMd_CM].[dbo].[PhnPhone] ph
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[srvitem]si on ph.ItemID=si.ItemID
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[srvservice]sv on si.ServiceID=sv.ServiceID
				left join [OMNIA_ESUM_P_SUMd_CM].[dbo].[cusaccount]ca on sv.AccountID=ca.accountid
			where PhoneStatus in ('U','P')
			)pp
			on dd.AccountCode=pp.AccountCode
			where dd.AccountCode='' or dd.AccountID='' or coalesce(pp.phone,dd.Phone)=?)dd
			left join
			(SELECT distinct E.ID,E.TYPE,E.Status,E.MfgId as sSerialNumber,
			f.MACADDRESS as sMAC,ET.Name AS EQUIPMENTTYPE_Name,CA.AccountCode,
			A.Zip as ADDRESS_Zip, C.SrvItem_ItemID AS iItemID,
			CASE WHEN vh.HeadendCode = 900 THEN 1000002
				WHEN vh.HeadendCode = 901 THEN 1000002
				WHEN vh.HeadendCode = 31 THEN 1000002
				WHEN vh.HeadendCode = 902 THEN 1000002
			ELSE 0000000 END AS iVideoSystemID
			FROM  [OMNIA_ESUM_P_SUMd_CM].[dbo].EQUIPMENT E
				JOIN [OMNIA_ESUM_P_SUMd_CM].[dbo].FACILITY F on F.VSYSTEM_Id = E.EndSYSTEM_Id
				JOIN [OMNIA_ESUM_P_SUMd_CM].[dbo].CIRCUIT C on C.Id = F.CIRCUIT_Id
				JOIN [OMNIA_ESUM_P_SUMd_CM].[dbo].ADDRESSJOIN A on A.Id = E.ADDRESS_Id
				JOIN [OMNIA_ESUM_P_SUMd_CM].[dbo].CusAccount CA on CA.AccountID = E.ACCOUNT_Id
				JOIN [OMNIA_ESUM_P_SUMd_CM].[dbo].EQUIPMENTTYPE ET on ET.Id = E.EQUIPMENTTYPE_Id
				JOIN OMNIA_ESUM_P_SUM_CM.dbo.VidHeadend vh on e.VidHeadend_HeadendID=vh.HeadendID
			where E.Status LIKE 'active'
			)fm
			on dd.AccountCode=fm.AccountCode
		where ServiceStatus in ('a','n','2')