Weighted Activity Gauge, 1st Draft

0

Please login or register to vote for this query.

(click on this box to dismiss)

Windows Phone Meta

Q&A about the site for enthusiasts and power users of Windows Phone OS

SELECT 
  pt.Name as [PostType], 
  p.Id as [Post Link],
  p.Score,
  3 - p.PostTypeId + 
  -- Comment out the case if answers should have weight 1, 
  -- regardless of if they are the accepted answer.
    CASE
      WHEN p2.AcceptedAnswerId = p.Id 
      THEN 2
      ELSE 0
    END AS Weight
FROM Posts p
JOIN PostTypes pt
ON p.PostTypeId = pt.Id
LEFT JOIN Posts p2
ON p.ParentId = p2.Id
/*
Unused - easily expandable to be able set actual weights for question types
(faking "Accepted Answers" as PostTypeId = 3).

JOIN (
  SELECT 1 AS PostTypeId, 'Question' AS WeightType, 2 as WeightValue
  UNION ALL
  SELECT 2, 'Answer', 1
  UNION ALL
  SELECT 3, 'Accepted Answer', 3
  ) Weights
*/
WHERE p.OwnerUserId = 
  (
    SELECT TOP 1 Id 
    FROM Users 
    WHERE DisplayName = ##DisplayName:string##
  ) and
  --p.OwnerUserId = ##UserId## AND
  p.CreationDate BETWEEN 
  convert(Date, ##StartDate:string##) and convert(Date, ##EndDate:string##)

Enter Parameters

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