THE PROBLEM

So, some time has passed since you've implemented SMS or ConfigManager and you've got teams of people deploying ads left and right.  Now you're starting to notice ads all over the place.  As you look into things, you realize some of them are expired...so then you start wondering, "I wonder how many expired ads I have out there, and what collections are they advertised to?"

So, I've put together a first draft of a query that should do just that.

THE SOLUTION

Obviously, we're going to want to query the view named v_Advertisement, but since we'll want the name of the collection, not just the collectionID, we'll join to the view named v_Collection as well.

So, for an advertisement to be expired, you need two things:  
* The ExpirationTimeEnabled feild needs to be 2
* If the ExpirationTimeIsGMT flag is 0, the ExpirationTime needs to be less than the current time, indicating that the ExpirationTime has passed
  If the ExpirationTimeIsGMT flag is 1, the ExpirationTime needs to be less than the current UTC (GMT) time, indicating that the Expiration Time (in GMT) has passed the current time (in GMT)

Let's put this into a query:

SELECT
ad.AdvertisementID,
ad.AdvertisementName,
ad.PackageID,
ad.ProgramName,
col.CollectionID,
col.Name
FROM
dbo.v_Advertisement ad
JOIN dbo.v_Collection col
ON ad.CollectionID = col.CollectionID
WHERE
ExpirationTimeEnabled = 2
AND ((ExpirationTime < GETDATE() AND PresentTimeIsGMT = 0)
OR
(ExpirationTime < GETUTCDATE() AND PresentTimeIsGMT = 1))

That's really all there is to it.  The GETUTCDATE() pulls back the current date/time in UTC (which is also known as Greenwich Mean Time, GMT) so there's an OR in the WHERE clause to compare the expiration time to the current date if PresentTimeIsGMT = 0 and compare it to the current date in UTC if PresentTimeIsGMT = 1.

Hope this is helpful.