Q&A for professionals, academics, and students working within the systems development life cycle
-- Vanity search: links to my website posted by other people during last 2 months WITH mylink AS ( SELECT id, REPLACE(WebsiteUrl, 'http://', '') AS site FROM users WHERE id = ##UserId## ), mylink2 AS ( SELECT id, CASE SUBSTRING(site, LEN(site), 1) WHEN '/' THEN SUBSTRING(site, 0, LEN(site)) ELSE site END AS se FROM mylink ) SELECT p.id AS [Post Link], p.LastActivityDate AS [Last Activity] FROM ( SELECT p.id FROM mylink2 ml JOIN posts p ON p.body LIKE '%' + se + '%' ESCAPE '!' AND p.OwnerUserId <> ml.id WHERE p.LastActivityDate >= DATEADD(m, -2, GETDATE()) AND se <> '' AND se IS NOT NULL UNION SELECT c.PostId FROM mylink2 ml JOIN comments c ON c.text LIKE '%' + se + '%' ESCAPE '!' AND c.UserId <> ml.id WHERE c.CreationDate >= DATEADD(m, -2, GETDATE()) AND se <> '' AND se IS NOT NULL ) q JOIN posts p ON p.id = q.id ORDER BY p.LastActivityDate DESC