1

Please login or register to vote for this query.

(click on this box to dismiss)

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

-- CoTag: Questions tagged with [homework] and:
-- NumTags: Total number of tags on the question:
-- OpenQs: Include open questions? (Y/N)
-- ClosedQs: Include closed questions? (Y/N)
DECLARE @CoTag nvarchar(100) = ##CoTag:string##
DECLARE @NumTags int = ##NumTags:int##
DECLARE @OpenQs nvarchar = ##OpenQs:string?Y##
DECLARE @ClosedQs nvarchar = ##ClosedQs:string?N##


SELECT q.Id as [Post Link], q.Score, q.Tags, q.ClosedDate

FROM Posts q

-- PostTypeId = 1 --> only questions
-- q.Tags is formatted, e.g., '<tag1><tag2><tag3>'
-- The '%' is an SQL wildcard, similar to RegEx '*', matching zero or more
--   characters
-- The LEN() - LEN(REPLACE()) is a check for the number of tags, which works by
--   removing all instances of the '><' between two tags and probing the amount
--   by which the length of the q.Tags string changed
-- The two q.ClosedDate conditionals allow for free selection of open Q's,
--   closed Q's, or both 
WHERE q.PostTypeId = 1 AND q.Tags LIKE '%<homework>%' 
                       AND q.Tags LIKE '%<' + @CoTag + '>%'
                       AND LEN(q.Tags) - LEN(REPLACE(q.Tags, '><', '')) = 2 * (@NumTags-1)
                       AND 
         (
           (q.ClosedDate IS NULL AND UPPER(@OpenQs) = 'Y')
         OR
           (q.ClosedDate IS NOT NULL AND UPPER(@ClosedQs) = 'Y')
         )

fork query

edited mar 26 17

created mar 24 17 hBy2Py