1

Please login or register to vote for this query.

(click on this box to dismiss)

Preguntas más respondidas.

Q&A about the site for administrators, end users, developers and designers for Craft CMS

DECLARE @ultimo_anio int = YEAR(GETDATE());
DECLARE  @ultimo_mes int = MONTH(GETDATE());

DECLARE @anio int = ##Anio?@ultimo_anio##;
DECLARE @mes int =   ##Mes?@ultimo_mes##;

DECLARE @anio_mes VARCHAR(6);
SET @anio_mes = CONCAT(@anio,RIGHT('00'+CONVERT(VARCHAR(2),@mes),2));

WITH Answers AS (
  SELECT ParentId AS Id, max(Score) AS Score
  FROM Posts
  WHERE PostTypeId = 2
  GROUP BY ParentId
)

SELECT TOP 100
  format(Q.CreationDate,'dd/MM/yy') "Creación",
  CONCAT('site://q/',Q.Id,'|',Q.Title) Pregunta,
  Q.AnswerCount "Cantidad de Respuestas",
  Q.Score "Votos de Pregunta",
  A.Score "Votos de Mejor Respuesta"

FROM Posts Q
LEFT OUTER JOIN Answers A
  ON Q.Id = A.Id
WHERE
  1=1
  and ParentId is null
  and Q.CreationDate >= DATEADD(DAY,-1,DATEADD(MONTH,-1,@anio_mes+'01'))
  and Q.CreationDate < @anio_mes + '01'

ORDER BY
  3 DESC,
  4 DESC,
  5 DESC,
  1 DESC,
  Q.Title ASC
;

fork query

edited oct 6 16 ArtEze

created oct 4 16 Mariano