0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

SELECT
   SUBSTRING(p.[Title], (PATINDEX('%[OT][RN][AS]-[0-9][0-9][0-9][0-9][0-9 ]%',p.[Title])),9),
   AnswerCount = SUM(p.AnswerCount),
   ViewCount = SUM(p.ViewCount),
   CommentCount = SUM(p.CommentCount),
   MinCD = MIN(p.CreationDate),
   MaxCD = MAX(p.CreationDate),
   UpVote = SUM(CASE WHEN vt.name = 'UpMod' THEN 1 ELSE 0 END), 
   Favorite = SUM(CASE WHEN vt.name = 'Favorite' THEN 1 ELSE 0 END),
   OtherVote = SUM(CASE WHEN vt.name NOT IN ('UpMod', 'Favorite') THEN 1 ELSE 0 END)
   FROM Posts p
      INNER JOIN Votes v ON p.Id = v.PostId
      INNER JOIN VoteTypes vt ON v.VoteTypeId = vt.Id
   WHERE vt.Name NOT IN ('DownMod', 'Offensive', 'Deletion', 'Spam')
   /*AND p.Tags LIKE '%oracle%'*/ AND (p.title LIKE '%ORA-%' OR p.title LIKE '%TNS-%')
   GROUP BY SUBSTRING(p.[Title], (PATINDEX('%[OT][RN][AS]-[0-9][0-9][0-9][0-9][0-9 ]%',p.[Title])),9)
   ORDER BY ViewCount DESC;

fork query

edited jul 1 22 jon.doe39361

created oct 22 20 gvenzl