Q&A for professional and enthusiast programmers
SELECT c.CreationDate, CASE WHEN (2 = p.PostTypeId) THEN 'Answer' WHEN (1 = p.PostTypeId) THEN 'Question' ELSE 'UNKNOWN' END AS Type, c.Id [Comment Link], c.Score, n.TotalComments, CASE WHEN (2 = p.PostTypeId) THEN t.Title ELSE p.Title END AS Title FROM Comments c LEFT OUTER JOIN Posts p on (c.PostId = p.Id) LEFT OUTER JOIN Posts t on (p.ParentId = t.Id) LEFT OUTER JOIN (SELECT c.PostId, COUNT(c.PostId) TotalComments FROM Comments c GROUP BY c.PostId) n on (c.PostId = n.PostId) WHERE (1 = p.PostTypeId or 2 = p.PostTypeId) AND -- only needed on Stack Overflow --c.CreationDate >= DATEADD(DAY, -120, GETDATE()) AND ( UPPER(c.Text) LIKE UPPER('%thank%') OR (UPPER(c.Text) LIKE UPPER('%welcome%') AND NOT UPPER(c.Text) LIKE UPPER('%happy%') ) -- End of query ) ORDER BY p.PostTypeId DESC, c.Id DESC