SQL query to get packages which are advertised without updating DPs

SELECT dbo.v_Advertisement.AdvertisementID, dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.CollectionID,dbo.v_Advertisement.PresentTime, dbo.v_Advertisement.ExpirationTime, dbo.v_Advertisement.SourceSite, dbo.v_Package.PackageID,
dbo.v_Package.Name, dbo.v_Package.Version, dbo.v_Package.SourceVersion, dbo.v_Package.SourceDate, dbo.v_Package.LastRefreshTime,
dbo.v_DistributionPoint.ServerNALPath, dbo.v_DistributionPoint.SiteCode, dbo.v_DistributionPoint.SiteName,
dbo.v_DistributionPoint.LastRefreshTime AS Expr1, dbo.v_DistributionPoint.Status, dbo.v_PackageStatusDistPointsSumm.ServerNALPath AS Expr2,
dbo.v_PackageStatusDistPointsSumm.SourceVersion AS Expr3, dbo.v_PackageStatusDistPointsSumm.SiteCode AS Expr4,
dbo.v_PackageStatusDistPointsSumm.State, dbo.v_PackageStatusDistPointsSumm.LastCopied, dbo.v_PackageStatusDistPointsSumm.SummaryDate,
dbo.v_PackageStatusDistPointsSumm.InstallStatus
FROM dbo.v_Advertisement INNER JOIN
dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
dbo.v_DistributionPoint ON dbo.v_Package.PackageID = dbo.v_DistributionPoint.PackageID INNER JOIN
dbo.v_PackageStatusDistPointsSumm ON dbo.v_DistributionPoint.PackageID = dbo.v_PackageStatusDistPointsSumm.PackageID
WHERE dbo.v_PackageStatusDistPointsSumm.state=1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s