1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

-- UserID: Search the favorites of user ID:
-- OnlyNoAccepted: Return only questions with no accepted answer (Y/N)?
-- OnlyNotAnswered: Return only questions where above user has not answered (Y/N)?
-- VoteAge: Return only questions favorited more than ## days ago:
DECLARE @UserID int = ##UserID:int##
DECLARE @OnlyNoAccepted nvarchar = ##OnlyNoAccepted:string?N##
DECLARE @OnlyNotAnswered nvarchar = ##OnlyNotAnswered:string?Y##
DECLARE @MinFavAge int = ##VoteAge:int?15##


SET @OnlyNoAccepted = UPPER(@OnlyNoAccepted)
SET @OnlyNotAnswered = UPPER(@OnlyNotAnswered)


SELECT p.Id AS [Post Link], MAX(p.Score) AS 'Score',
    IIF(MAX(p.AcceptedAnswerId) IS NULL, 'No', 'Yes') AS 'Accepted Answer?',
    'site://a/' + CAST(MAX(a.Id) AS nvarchar(10)) + '|Link' AS 'User Answer',
    'site://q/' + CAST(MAX(a.ParentId) AS nvarchar(10)) + '|link' AS 'Answer-Parent'


FROM Posts p
LEFT JOIN Votes v ON v.VoteTypeId = 5 AND v.PostId = p.Id
LEFT JOIN Posts a ON a.PostTypeId = 2 AND a.ParentId = p.Id AND a.OwnerUserId = @UserID


WHERE p.PostTypeId = 1 AND
    (p.AcceptedAnswerId IS NULL OR @OnlyNoAccepted != 'Y') AND
    v.UserId = @UserID AND
--    ((SELECT p2.Id FROM Posts p2 WHERE p2.PostTypeId = 2 AND p2.ParentId = p.Id AND p2.OwnerUserId = @UserId) IS NULL
--      OR @OnlyNotAnswered != 'Y')
    (a.Id IS NULL OR @OnlyNotAnswered != 'Y') AND 
    DATEDIFF(day, v.CreationDate, CURRENT_TIMESTAMP) > @MinFavAge


GROUP BY p.Id

ORDER BY MAX(p.Score) ASC

fork query

edited may 24 17

created may 5 17 hBy2Py