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 em Português

Perguntas e respostas para programadores profissionais e entusiastas

SELECT count(p.ID) AS qtd,
       CONVERT(DATETIME, CONVERT(VARCHAR(7), p.CreationDate, 120) + '-01') AS data_mes,
       CONVERT(DATETIME, CONVERT(VARCHAR(7), p.ClosedDate, 120) + '-01') AS mes_closed,
       sum(p.AnswerCount) qtd_respostas,
       sum(p.FavoriteCount) qtd_fav,
       sum(p.ViewCount) qtd_view,
       CONVERT(DATETIME, CONVERT(VARCHAR(7), p.DeletionDate, 120) + '-01') AS mes_deleted,
       p.PostTypeId,
       ph.PostHistoryTypeId,
       pht.Name AS HistoryTypeName,
       Year(p.CreationDate) AS ano,
       Month(p.CreationDate) AS mes,
       ph.Comment AS motivo,
       isnull(rt.Name,'Aberta') as Name,
       pht.Id AS hist_type,
  (SELECT count(1)
   FROM posts x
   WHERE year(x.CreationDate) = year(p.CreationDate)
     AND month(x.CreationDate) = month(p.CreationDate) ) AS total,

  (SELECT count(1)
   FROM posts x
   WHERE year(x.CreationDate) = year(p.CreationDate)
     AND month(x.CreationDate) = month(p.CreationDate)
     AND x.ClosedDate IS NOT NULL) AS total_closed,
     (case when p.AcceptedAnswerId is null then 'F' else 'T' end) as respondida,
     (case when p.DeletionDate is null then 'F' else 'T' end) as deletada,
     (case when p.AnswerCount > 0 then 'T' else 'F' end) as tem_respostas
FROM PostsWithDeleted p
LEFT JOIN PostHistory ph ON p.Id = ph.PostId
AND ph.PostHistoryTypeID = 10
LEFT JOIN PostHistoryTypes pht ON pht.Id = ph.PostHistoryTypeID
LEFT JOIN CloseReasonTypes rt ON ph.Comment = rt.Id
WHERE p.PostTypeId = 1
GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(7), p.CreationDate, 120) + '-01'),
         CONVERT(DATETIME, CONVERT(VARCHAR(7), p.ClosedDate, 120) + '-01'),
         CONVERT(DATETIME, CONVERT(VARCHAR(7), p.DeletionDate, 120) + '-01'),
         p.PostTypeId,
         ph.PostHistoryTypeId,
         pht.Name,
         Year(p.CreationDate),
         Month(p.CreationDate),
         ph.Comment,
         pht.Id,
         isnull(rt.Name,'Aberta'),
         rt.Description,
         (case when p.AcceptedAnswerId is null then 'F' else 'T' end),
         (case when p.DeletionDate is null then 'F' else 'T' end),
         (case when p.AnswerCount > 0 then 'T' else 'F' end)
ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR(7), p.CreationDate, 120) + '-01') DESC

Enter Parameters

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