Spelling Bee

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chemistry

Q&A for scientists, academics, teachers, and students in the field of chemistry

DECLARE @Question tinyint = 1;
DECLARE @Answer tinyint = 2;
DECLARE @TagWikiExcerpt tinyint = 4;
DECLARE @TagWiki tinyint = 5;
DECLARE @PostLocked tinyint = 14;
DECLARE @PostUnlocked tinyint = 15;

WITH Common as (
  Select '%thank you%' as Value
  UNION
  SELECT '%i %see %thanks%'        -- ok i see thanks
),
MyPosts (Id, Title, STitle, SBody)  
AS  
(  
SELECT 
  Posts.Id,
  Posts.Title,
  (
    SELECT count(*) FROM Common
    WHERE lower(Posts.Title) Like Common.Value
  ) AS STitle,
  (
    SELECT count(*) FROM Common
    WHERE lower(Posts.Body) Like Common.Value
  ) AS SBody
  FROM Posts
  WHERE Posts.Score >= ##Score:int##
    AND Posts.ClosedDate IS NULL
    AND Posts.DeletionDate IS NULL
    AND (
            (
                Posts.PostTypeId = @Question
                AND Posts.Id <> ALL (
                    SELECT DISTINCT PostId
                    FROM PostHistory
                    WHERE
                        PostId = PostHistory.PostId
                        AND PostHistoryTypeId IN (@PostLocked, @PostUnlocked)
                    GROUP BY PostId
                        HAVING COUNT(PostHistoryTypeId) % 2 = 1
                )
            )
            OR
            (
                Posts.PostTypeId = @Answer
                AND Posts.ParentId <> ALL (
                    SELECT DISTINCT PostId
                    FROM PostHistory
                    WHERE
                        PostId = Posts.ParentId
                        AND PostHistoryTypeId IN (@PostLocked, @PostUnlocked)
                    GROUP BY PostId
                        HAVING COUNT(PostHistoryTypeId) % 2 = 1
                )
            ) -- Locked posts can't be edited
            OR Posts.PostTypeId IN (@TagWikiExcerpt, @TagWiki)
        )
)

SELECT TOP 50 Id as [Post Link]
FROM MyPosts

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results