I have looked at this query/report, and from the beginning I figured it must be missing something. I looked through the SQL, and can't identify it off-hand, so I figured I would build my own query, and see how they compare. The results I got are very different... :(
Edit: Found the problem
You cannot chain two outer joins.... Consider the query for the missing
We know from the alternate query, and from Quentin's profile, that he offered a 150 bounty. This 150 does not show up on your query.
Here is a base query that matches your query and it should show this bounty.
it does not. But, if we convert the final outer-joins on alias
a and alias
bc to an equi-join, and make it a
with statement, it all of a sudden works....
The reason is because we need the two outer joins, and the first one succeeds. The first one gets answers to the question (there may not be answers, so we need the outer join). The second outer-join looks for bounty-votes, and the bounty may not be awarded.
I am not sure why this is not working, could be a SQLServer bug?
Second update This issue also accounts for the missing 2 bounties from 200_success... because they are on questions which have answers, but the answers were not awarded the bounty.
This also explains why some offered-but-not-awarded bounties are working, because they were on questions with no answers.
Alternate query here
I have put together this report BountifulII. Obviously, there could be problems with my report too (offer it as a question? - Week-end challenge?).
Note that my report has a couple of odd
--firewall fix comments in it, see this MSO Question for the reason... ;-)
My report does a more general process of calculating who offered bounties, and who was awarded bounties. It separates out awarded-bounties to those awarded to their own questions, and those awarded to any question). In the final report it lists the
PromotedAmount. These values should match with the values in your reports.... but they do not.
here is the revised SQL:
WITH Bounties AS (
UserID AS UserID,
0 AS GetCount,
0 AS GetAmount,
COUNT(BountyAmount) AS GiveCount,
SUM(BountyAmount) AS GiveAmount,
SUM(case when Posts.OwnerUserId = Votes.UserId then 1 else 0 end) AS SelfCount,
SUM(case when Posts.OwnerUserId = Votes.UserId then BountyAmount else 0 end) AS SelfAmount
WHERE Votes.PostId = Posts.Id
AND VoteTypeId = 8
GROUP --firewall fix
Posts.OwnerUserID AS UserID,
COUNT(BountyAmount) AS GetCount,
SUM(BountyAmount) AS GetAmount,
0 AS GiveCount,
0 AS GiveAmount,
0 AS SelfCount,
0 AS SelfAmount
WHERE Votes.PostId = Posts.ID
AND VoteTypeId = 9
GROUP --firewall fix
SELECT UserID AS [User Link],
SUM(GiveCount - SelfCount) AS Pomotions,
SUM(GiveAmount - SelfAmount) AS PromotedAmount,
SUM(GetCount) AS GetCount,
SUM(GetAmount) AS GetAmount,
SUM(GiveCount) AS GiveCount,
SUM(GiveAmount) AS GiveAmount,
SUM(SelfCount) AS SelfCount,
SUM(SelfAmount) AS SelfAmount,
SUM(GiveCount + GetCount) AS EventCount,
SUM(GetAmount - GiveAmount) AS NetBenefit
GROUP -- firewall fix
ORDER BY PromotedAmount DESC, EventCount DESC, NetBenefit DESC