10

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for amateur radio enthusiasts

-- 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

fork query

created may 27 10 Quassnoi