Vanity search: links to my website posted by other people during last 2 months

0

Please login or register to vote for this query.

(click on this box to dismiss)

Vanity search: links to my website posted by other people during last 2 months

Stack Overflow

Q&A for programmers

-- Vanity search: links to my website posted by other people during last 2 months

WITH    mylink AS
        (
        SELECT  id, REPLACE(WebsiteUrl, 'http://', '') AS replaced
        FROM    users
        WHERE   id = ##UserId##
        ),
        mylink2 AS
        (
        SELECT  id, replaced, CHARINDEX('/', replaced) AS fs
        FROM    mylink
        ),
        mylink3 AS
        (
        SELECT  id, REPLACE(CASE fs WHEN 0 THEN replaced ELSE LEFT(replaced, fs - 1) END, '.', '!.') AS se
        FROM    mylink2
        )
SELECT  p.id AS [Post Link], p.LastActivityDate AS [Last Activity]
FROM    (
        SELECT  p.id
        FROM    mylink3 ml
        JOIN    posts p
        ON      p.body LIKE '%' + se + '%' ESCAPE '!'
                AND p.OwnerUserId <> ml.id
        WHERE   p.LastActivityDate >= DATEADD(m, -2, GETDATE())
        UNION
        SELECT  c.PostId
        FROM    mylink3 ml
        JOIN    comments c
        ON      c.text LIKE '%' + se + '%' ESCAPE '!'
                AND c.UserId <> ml.id
        WHERE   c.CreationDate >= DATEADD(m, -2, GETDATE())
        ) q
JOIN    posts p
ON      p.id = q.id
ORDER BY
        p.LastActivityDate DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results