I wanted to figure out how to do date compares in SQL, and generate a report showing users expiring in the next 31 days:

SELECT
  u.LastName AS [Last Name],
  u.FirstName AS [First],
  r.Rolename AS Type,
  CONVERT(VARCHAR, ur.EffectiveDate,1) AS Joined,
  CONVERT(VARCHAR,ur.ExpiryDate,1) AS Expires,
  upd.Street,
  upd.Unit,
  upd.City,
  upd.Region AS State,
  upd.Zip,
  upd.Phone,
  u.Email
FROM dbo.Users as u INNER JOIN
  dbo.UserRoles AS ur ON u.UserID = ur.UserID  INNER JOIN
  dbo.Roles AS r ON ur.RoleID = r.RoleID
   AND DATEDIFF(day, GETDATE(), ur.ExpiryDate) <= 31
    LEFT OUTER JOIN
  (SELECT      
    up.UserID,  
    MAX(CASE WHEN ppd.PropertyName = 'Street' THEN up.PropertyValue ELSE '' END) AS Street, 
    MAX(CASE WHEN ppd.PropertyName = 'Unit' THEN up.PropertyValue ELSE '' END) AS Unit, 
    MAX(CASE WHEN ppd.PropertyName = 'City' THEN up.PropertyValue ELSE '' END) AS City, 
    MAX(CASE WHEN ppd.PropertyName = 'Region' THEN up.PropertyValue ELSE '' END) AS Region, 
    MAX(CASE WHEN ppd.PropertyName = 'PostalCode' THEN up.PropertyValue ELSE '' END) AS Zip, 
    MAX(CASE WHEN ppd.PropertyName = 'Telephone' THEN up.PropertyValue ELSE '' END) AS Phone 
   FROM           
    dbo.UserProfile AS up INNER JOIN 
    dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID AND ppd.PortalID = 0 
   GROUP BY up.UserID) AS upd ON u.UserID = upd.UserID 
ORDER BY ur.ExpiryDate