Config Manager 2007 SQL Query for Advertisement Status

SQL query for pulling a list of client status information for a given advertisement in a Configuration Manager 2007 site database.  The columns returned are:

Name, ResourceID, Status, Status Detail, StatusTime, OS Name, Service Pack, AD Site Name

It also converts the UTC time value for LastStatusTime to local time, with DST offset.  Enjoy!

SELECT dbo.v_R_System.Netbios_Name0 AS Name, 
 dbo.v_ClientAdvertisementStatus.ResourceID, 
 dbo.v_ClientAdvertisementStatus.LastStateName AS [Status], 
 dbo.v_ClientAdvertisementStatus.LastStatusMessageIDName AS [Status Detail],
 CONVERT(datetime, 
            SWITCHOFFSET(

                CONVERT(datetimeoffset, 

                    dbo.v_ClientAdvertisementStatus.LastStatusTime),
  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS StatusTime, 
 dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OSName, 
 dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS SP, 
 dbo.v_R_System.AD_Site_Name0 AS SiteName 
FROM dbo.v_ClientAdvertisementStatus LEFT OUTER JOIN 
 dbo.v_GS_OPERATING_SYSTEM ON dbo.v_ClientAdvertisementStatus.ResourceID = 
 dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN 
 dbo.v_R_System ON dbo.v_ClientAdvertisementStatus.ResourceID = dbo.v_R_System.ResourceID 
WHERE (dbo.v_ClientAdvertisementStatus.AdvertisementID = 'ABC20778') 
ORDER BY Name

This came from a project I've been working on.