Created
September 6, 2016 14:44
-
-
Save anonymous/a6a693046c154a8763ba1d3906c13687 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment