,p.ProviderFirstName + ' ' + p.ProviderLastName AS ReferredFrom
,p.ProviderNPINumber AS ReferringNPI
,p.ProviderType
,p.ProviderTypeCategory
,cp.ClinicalProviderID
,cp.Name
,cp.NPI AS ReferredToNPI
,d.ClinicalEncounterID
,d.DocumentClass
,d.ClinicalOrderType
,d.ClinicalOrderGenus
,d.[Status]
,d.OrderDatetime
,sfp.chapter
,sfp.pod
,sfp.specialty
,sfp.hospital_affiliation
,CASE WHEN sfp1.NPI IS NULL AND (sfp1.ntwk_term_dt IS NULL OR sfp1.ntwk_term_dt >= d.CreatedDatetime) THEN 'Yes' ELSE 'No' END AS 'In Network'
FROM [Athena_DW].[Athena].[Document] d
INNER JOIN Athena_DW.Athena.[Provider] p ON d.ProviderUsername = p.ProviderUserName
INNER JOIN Athena_DW.Athena.ClinicalProvider cp ON d.ClinicalProviderID = cp.ClinicalProviderID
LEFT OUTER JOIN sandbox.dbo.vAllProviders sfp ON p.ProviderNPINumber = sfp.NPI and sfp.prov_ty='PCP'--Referring Provider LEFT OUTER JOIN sandbox.dbo.vAllProviders sfp1 ON convert(varchar(30),cp.NPI) = convert(varchar(30),sfp1.NPI) --Referred to Provider --deleted from sfp1, wasn't complete code, wonder if that was needed where documentClass='ORDER'
and ClinicalOrderTypeGroup='CONSULT'
and p.RowIsCurrent='Y'
AND d.RowIsCurrent = 'Y'
AND cp.RowIsCurrent = 'Y'
AND d.DeletedBy IS NULL
AND p.DeletedBy IS NULL
and cp.DeletedDatetime IS NULL
and Status <> 'DELETED'
AND ClinicalOrderType LIKE '%REFER%'
AND d.OrderDatetime > '20160101'
AND cp.DuplicateOfClinicalProvID IS NULL ;
Anything I put after the last ; is causing an error. Even If I just add a blank new tab. Any ideas??
