Paul's Test Query

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping

Q&A for gardeners and landscapers

-- parameter(s)

    declare 
        @tagsCsv nvarchar(255) = 'c#,sql-server',
        @matchExactly bit = 1;

-- parse @tagsCsv

    declare @tags table (
        tag nvarchar(50) collate SQL_Latin1_General_CP1_CS_AS
    );

    insert     @tags
    select     '<' + value + '>'
    from       string_split(@tagsCsv, ',');

-- core qurey

    with

        getPosts as (

            select    
            top 10      p.id, 
                        title, body, tags,
                        acceptedAnswerId, answerCount,
                        score, viewCount,
                        creationDate, lastActivityDate,
                        th.tagHits,
                        tc.tagCount
            from        posts p
            cross apply (
                            select tagHits = count(*) 
                            from @tags t 
                            where p.tags like '%' + t.tag + '%'
                        ) th
            cross apply (
                            select tagCount = 
                                len(tags) 
                                - len(replace(tags, '>', ''))
                        ) tc 
            where       deletionDate is null
            and         closedDate is null
            and         parentId is null -- it's the question
            and         th.tagHits > 0

        )

        select    id, 
                  title, body, tags, 
                  answers = answerCount,
                  hasAaccepted = iif(acceptedAnswerId is null, 0, 1), 
                  score, viewCount,
                  creationDate, lastActivityDate,
                  tagHits, tagCount
        from      getPosts
        where     @matchExactly = 0 or tagHits = tagCount

Enter Parameters

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