select
pkg.Name as C062,
pkg.PackageID,
SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ) AS C070,
dp.SiteCode,
dp.LastRefreshTime,
stat.SourceVersion,
stat.LastCopied,
stat.SummaryDate,
stat.InstallStatus,
case when dp.IsPeerDP=1 then ‘*’ else ” end as BranchDP
from v_Package pkg
join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath
and dp.PackageID=stat.PackageID
where stat.State!=0 and DP.SiteCode in (‘sitecode‘)
Advertisements
