1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

WITH PorSemana AS (
        select --Fechas por semana
               dateadd(week, datediff(day, '20000109', CreationDate) / 7, '20000109') Fecha,
               ReviewTaskTypeId,
               case when DeletionDate is null then null else DATEDIFF(mi, CreationDate, DeletionDate) end AS Demora
          from ReviewTasks
               --En los últimos N meses
         where CreationDate >= dateadd(month, -##CantMeses:int?6##, GETDATE())
               --Excepto la última semana incompleta
           and CreationDate < dateadd(week, datediff(day, '20000109', GETDATE()) / 7, '20000109')
)

select
       Fecha,
       SUM(case when ReviewTaskTypeId = 4 then ##VerPrimeras:int?1## else 0 end) / 7 [Primeras publicaciones (por día)],
       AVG(case when ReviewTaskTypeId = 4 and ##VerDemoraPrimeras:int?1## = 1 then Demora else null end) [Demora Media Primeras publicaciones (min)],
       SUM(case when ReviewTaskTypeId = 1 then ##VerEdiciones:int?1## else 0 end) / 7 [Ediciones sugeridas (por día)],
       AVG(case when ReviewTaskTypeId = 1 and ##VerDemoraEdiciones:int?0## = 1 then Demora else null end) [Demora Media Ediciones sugeridas (min)],
       SUM(case when ReviewTaskTypeId = 2 then ##VerCierre:int?1## else 0 end) / 7 [Votos de cierre (por día)],
       AVG(case when ReviewTaskTypeId = 2 and ##VerDemoraCierre:int?0## = 1 then Demora else null end) [Demora Media Votos de cierre (min)],
       SUM(case when ReviewTaskTypeId = 3 then ##VerBajaCalidad:int?1## else 0 end) / 7 [Publicaciones de baja calidad (por día)],
       AVG(case when ReviewTaskTypeId = 3 and ##VerDemoraBajaCalidad:int?0## = 1 then Demora else null end) [Demora Media Publicaciones de baja calidad (min)],
       SUM(case when ReviewTaskTypeId = 5 then ##VerTardias:int?1## else 0 end) / 7 [Respuestas tardías (por día)],
       AVG(case when ReviewTaskTypeId = 5 and ##VerDemoraTardias:int?0## = 1 then Demora else null end) [Demora Media Respuestas tardías (min)],
       SUM(case when ReviewTaskTypeId = 6 then ##VerReapertura:int?1## else 0 end) / 7 [Votos de reapertura (por día)],
       AVG(case when ReviewTaskTypeId = 6 and ##VerDemoraReapertura:int?0## = 1 then Demora else null end) [Demora Media Votos de reapertura (min)]
  from PorSemana
 group by Fecha
 order by Fecha desc

view current version

edited feb 21 18

created feb 21 18 Mariano