Extração de dados para relatório - Perguntas e fechadas e deletadas

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

with totais as (
SELECT 
p.OwnerUserId,
count(p.ID) AS qtd,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.AcceptedAnswerId is null) as total_nao_respondida,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.AcceptedAnswerId is not null) as total_respondida,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.DeletionDate is not null) as total_deletado,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.ClosedDate is not null) as total_closed,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.AnswerCount = 0) as total_sem_resposta,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.AnswerCount > 0) as total_com_resposta,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.Score < 0) as total_com_negativos,
(select count(x.ID) from PostsWithDeleted x where x.PostTypeId = 1 and x.OwnerUserId = p.OwnerUserId and x.Score > 0) as total_com_positivos
FROM PostsWithDeleted p
WHERE p.PostTypeId = 1 and p.OwnerUserId = ##UserId##
group by p.OwnerUserId)


select
t.OwnerUserId,
(t.total_nao_respondida * 100 / t.qtd) as perc_nao_respondida,
(t.total_closed * 100 / t.qtd) as perc_closed,
(t.total_com_negativos * 100 / t.qtd) as perc_negativas,
(t.total_com_positivos * 100 / t.qtd) as perc_positivas,
(t.total_sem_resposta * 100 / t.qtd) as perc_sem_resposta,
(t.total_com_resposta * 100 / t.qtd) as perc_com_resposta

from totais t

Enter Parameters

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