none
Somatório de Backlog RRS feed

  • Pergunta

  • Olá galera,Estou precisando de um força com relação a um resultado que precisa trazer em um SELECT, mas não estou conseguindo.

    Basicamento eu preciso trazer uma informação de backlog por mês, e o calculo de backlog é simples, mas não estou tendo idéia de como implementa-lo no SELECT, segue script:

    SELECT     
    	CASE A.Mes
            WHEN 1 THEN 'Janeiro'
    	    WHEN 2 THEN 'Fevereiro'
    	    WHEN 3 THEN 'Março'
    	    WHEN 4 THEN 'Abril'
    	    WHEN 5 THEN 'Maio'
    	    WHEN 6 THEN 'Junho'
                WHEN 7 THEN 'Julho'
    	    WHEN 8 THEN 'Agosto'
    	    WHEN 9 THEN 'Setembro'		
    	    WHEN 10 THEN 'Outubro'
    	    WHEN 11 THEN 'Novembro'
            WHEN 12 THEN 'Dezembro'
    	 END AS 'Mes_Ano', A.Abertos, B.Fechados, (A.Abertos - B.Fechados) Backlog FROM
    (SELECT MONTH(IR."Data Abertura") AS 'Mes', 
    		COUNT(*) AS Abertos
    FROM vw_Incidentes_Requisicoes IR
    WHERE YEAR(IR."Data Abertura") = 2012
    GROUP BY MONTH(IR."Data Abertura")) A
    LEFT JOIN
    (SELECT MONTH(IR."Data Término Processo") AS 'Mes', 
    		COUNT(*) AS Fechados
    FROM vw_Incidentes_Requisicoes IR
    WHERE IR."Status" = 'Terminado' AND
    	  YEAR(IR."Data Término Processo") = 2012
    GROUP BY MONTH(IR."Data Término Processo")) B ON A.Mes = B.Mes
    GROUP BY A.Mes, A.Abertos, B.Fechados
    ORDER BY A.Mes

    Resultado:

    Este resultado acima de backlog não é real, pois o que deve ocorrer é que em Fevereiro o backlog é igual a 12 + 128 = 140, em Março o backlogo é igual a 140 + 5 = 145, em Abril é igual a 145 - 2 = 143, em Maio é igual a 143 - 14 = 129 e assim por diante.

    Tem alguma forma de fazer este cálculo no SELECT ?

    Obrigado!!!


    Ricardo Filho

    quinta-feira, 2 de agosto de 2012 18:55

Respostas

  • Ricardo,

    substitua a tabela temporaria pelas suas tabelas, você já tinha chego nos valores que eu inseri na mão

    tente assim

    ;WITH CTE_BACKLOG AS (
    SELECT     
    	CASE A.Mes , A.Abertos, B.Fechados FROM
    (SELECT MONTH(IR."Data Abertura") AS 'Mes', 
    		COUNT(*) AS Abertos
    FROM vw_Incidentes_Requisicoes IR
    WHERE YEAR(IR."Data Abertura") = 2012
    GROUP BY MONTH(IR."Data Abertura")) A
    LEFT JOIN
    (SELECT MONTH(IR."Data Término Processo") AS 'Mes', 
    		COUNT(*) AS Fechados
    FROM vw_Incidentes_Requisicoes IR
    WHERE IR."Status" = 'Terminado' AND
    	  YEAR(IR."Data Término Processo") = 2012
    GROUP BY MONTH(IR."Data Término Processo")) B ON A.Mes = B.Mes
    GROUP BY A.Mes, A.Abertos, B.Fechados)
    SELECT     
    	CASE Mes
            WHEN 1 THEN 'Janeiro'
    	    WHEN 2 THEN 'Fevereiro'
    	    WHEN 3 THEN 'Março'
    	    WHEN 4 THEN 'Abril'
    	    WHEN 5 THEN 'Maio'
    	    WHEN 6 THEN 'Junho'
                WHEN 7 THEN 'Julho'
    	    WHEN 8 THEN 'Agosto'
    	    WHEN 9 THEN 'Setembro'		
    	    WHEN 10 THEN 'Outubro'
    	    WHEN 11 THEN 'Novembro'
            WHEN 12 THEN 'Dezembro'
    	 END AS 'Mes_Ano', Abertos, Fechados, 
    	 (SELECT SUM(ABERTOS) - SUM(Fechados) 
    		from CTE_BACKLOG 
    		where mes <= a.mes)   Backlog 
    	 FROM CTE_BACKLOG A
    ORDER BY A.Mes
    


    Att.
    Lukas Baldan

    • Marcado como Resposta Ricardo.Filho sexta-feira, 3 de agosto de 2012 13:23
    sexta-feira, 3 de agosto de 2012 12:52
  • Ok,

    O erro é que quando o ano for menor não se pode aplicar o filtro do mês

    tente assim

    where mes <= case when ano < A.ano THEN 12 else a.mes END
    and ano <= a.ano)   Backlog 
    


    Att.
    Lukas Baldan

    • Marcado como Resposta Ricardo.Filho segunda-feira, 6 de agosto de 2012 13:58
    segunda-feira, 6 de agosto de 2012 13:50
  • Nossa fiz correndo fiz errado rs.

    tente assim

    WHERE A.ANO >= @ANO 
    AND A.MES >= (CASE WHEN A.ANO > @ANO THEN 1 ELSE @MES END)


    Att.
    Lukas Baldan


    segunda-feira, 6 de agosto de 2012 17:28

Todas as Respostas

  • Olá Ricardo,

    Bem uma das formas de fazer e através de uma subquery que some o Backlog dos meses anteriores.

    Veja esse script

    CREATE TABLE #BACKLOG(
    MES INT,
    ABERTOS int,
    FECHADOS int)
    
    INSERt into #BACKLOG values(1,2112,2100)
    ,(2,2218,2090)
    ,(3,4335,4330)
    ,(4,3107,3109)
    ,(5,3460,3474)
    ,(6,692,418)
    
    SELECT     
    	CASE Mes
            WHEN 1 THEN 'Janeiro'
    	    WHEN 2 THEN 'Fevereiro'
    	    WHEN 3 THEN 'Março'
    	    WHEN 4 THEN 'Abril'
    	    WHEN 5 THEN 'Maio'
    	    WHEN 6 THEN 'Junho'
                WHEN 7 THEN 'Julho'
    	    WHEN 8 THEN 'Agosto'
    	    WHEN 9 THEN 'Setembro'		
    	    WHEN 10 THEN 'Outubro'
    	    WHEN 11 THEN 'Novembro'
            WHEN 12 THEN 'Dezembro'
    	 END AS 'Mes_Ano', Abertos, Fechados, 
    	 (SELECT SUM(ABERTOS) - SUM(Fechados) 
    		from #BACKLOG 
    		where mes <= a.mes)   Backlog 
    	 FROM #BACKLOG A


    Att.
    Lukas Baldan

    sexta-feira, 3 de agosto de 2012 12:31
  • Opa Lukas, obrigado pela resposta, mas tenho uma dúvida.

    Como isso ficaria automatizado, por que estou vendo no sei INSERT que você colocou na mão os valores para preencher a tabela temporária de backlog, preciso que isso possa ser executado a qualquer momento.

    Vlw!


    Ricardo Filho

    sexta-feira, 3 de agosto de 2012 12:40
  • Ricardo,

    substitua a tabela temporaria pelas suas tabelas, você já tinha chego nos valores que eu inseri na mão

    tente assim

    ;WITH CTE_BACKLOG AS (
    SELECT     
    	CASE A.Mes , A.Abertos, B.Fechados FROM
    (SELECT MONTH(IR."Data Abertura") AS 'Mes', 
    		COUNT(*) AS Abertos
    FROM vw_Incidentes_Requisicoes IR
    WHERE YEAR(IR."Data Abertura") = 2012
    GROUP BY MONTH(IR."Data Abertura")) A
    LEFT JOIN
    (SELECT MONTH(IR."Data Término Processo") AS 'Mes', 
    		COUNT(*) AS Fechados
    FROM vw_Incidentes_Requisicoes IR
    WHERE IR."Status" = 'Terminado' AND
    	  YEAR(IR."Data Término Processo") = 2012
    GROUP BY MONTH(IR."Data Término Processo")) B ON A.Mes = B.Mes
    GROUP BY A.Mes, A.Abertos, B.Fechados)
    SELECT     
    	CASE Mes
            WHEN 1 THEN 'Janeiro'
    	    WHEN 2 THEN 'Fevereiro'
    	    WHEN 3 THEN 'Março'
    	    WHEN 4 THEN 'Abril'
    	    WHEN 5 THEN 'Maio'
    	    WHEN 6 THEN 'Junho'
                WHEN 7 THEN 'Julho'
    	    WHEN 8 THEN 'Agosto'
    	    WHEN 9 THEN 'Setembro'		
    	    WHEN 10 THEN 'Outubro'
    	    WHEN 11 THEN 'Novembro'
            WHEN 12 THEN 'Dezembro'
    	 END AS 'Mes_Ano', Abertos, Fechados, 
    	 (SELECT SUM(ABERTOS) - SUM(Fechados) 
    		from CTE_BACKLOG 
    		where mes <= a.mes)   Backlog 
    	 FROM CTE_BACKLOG A
    ORDER BY A.Mes
    


    Att.
    Lukas Baldan

    • Marcado como Resposta Ricardo.Filho sexta-feira, 3 de agosto de 2012 13:23
    sexta-feira, 3 de agosto de 2012 12:52
  • Lukas,

    Não tem perigo de entrar em loop? Coloquei para executar e já passou de 2 minutos em execução sem responta... :s


    Ricardo Filho

    sexta-feira, 3 de agosto de 2012 13:02
  • Lukas,

    Funcionou cara, muito legal!

    Poderia me explicar melhor o artifícil utilizado para chegar a este resultado, gostaria não apenas de ter a resposta, mas de poder me deparar com outras situações semelhantes e conseguir desenvolver sozinho.

    Muito obrigado!


    Ricardo Filho

    sexta-feira, 3 de agosto de 2012 13:25
  • Ricardo,

    Então há varias maneiras de se fazer,

    no caso eu usei uma CTE para criar um resultado igual a tabela temporaria que eu criei no primeiro script.

    Tente excutar só a parte

    ;WITH CTE_BACKLOG AS (
    SELECT     
    	CASE A.Mes , A.Abertos, B.Fechados FROM
    (SELECT MONTH(IR."Data Abertura") AS 'Mes', 
    		COUNT(*) AS Abertos
    FROM vw_Incidentes_Requisicoes IR
    WHERE YEAR(IR."Data Abertura") = 2012
    GROUP BY MONTH(IR."Data Abertura")) A
    LEFT JOIN
    (SELECT MONTH(IR."Data Término Processo") AS 'Mes', 
    		COUNT(*) AS Fechados
    FROM vw_Incidentes_Requisicoes IR
    WHERE IR."Status" = 'Terminado' AND
    	  YEAR(IR."Data Término Processo") = 2012
    GROUP BY MONTH(IR."Data Término Processo")) B ON A.Mes = B.Mes
    GROUP BY A.Mes, A.Abertos, B.Fechados)
    SELECT  *	 FROM CTE_BACKLOG 
    

    ele vai trazer os dados do mes , abertos e fechados

    depois vem o subselect  com a diferença entre abertos e fechados dos meses anteriores a cada registro.

     (SELECT SUM(ABERTOS) - SUM(Fechados) 
    		from CTE_BACKLOG 
    		where mes <= a.mes)   Backlog 
    


    Att.
    Lukas Baldan

    sexta-feira, 3 de agosto de 2012 13:40
  • Bom Dia Lukas,

    Estava fazendo alguns testes com o script que vc ajudou a formatar e acho que talvez necessite de algum ajuste, gostaria de saber se vc pode me ajudar. Eu percebi que o script funciona para alguns casa, mas para outros não... Veja este caso abaixo:

    Script:

    WITH CTE_BACKLOG AS (
    SELECT     
    	A.Ano, A.Mes ,A.Abertos, B.Fechados FROM
    (SELECT YEAR(IR."Data Abertura") AS 'Ano',MONTH(IR."Data Abertura") AS 'Mes', 
    		COUNT(*) AS Abertos
    FROM vw_Incidentes_Requisicoes IR
    WHERE YEAR(IR."Data Abertura") >= 2011
    GROUP BY YEAR(IR."Data Abertura"), MONTH(IR."Data Abertura")) A
    LEFT JOIN
    (SELECT YEAR(IR."Data Término Processo") AS 'Ano',MONTH(IR."Data Término Processo") AS 'Mes', 
    		COUNT(*) AS Fechados
    FROM vw_Incidentes_Requisicoes IR
    WHERE IR."Status" = 'Terminado' AND
    	  YEAR(IR."Data Término Processo") >= 2011
    GROUP BY YEAR(IR."Data Término Processo"), MONTH(IR."Data Término Processo")) B ON A.Mes = B.Mes
    WHERE A.Ano = B.Ano
    GROUP BY A.Ano, A.Mes, A.Abertos, B.Fechados)
    
    SELECT A.Ano,    
    	CASE Mes
                WHEN 1 THEN 'Janeiro'
    	    WHEN 2 THEN 'Fevereiro'
    	    WHEN 3 THEN 'Março'
    	    WHEN 4 THEN 'Abril'
    	    WHEN 5 THEN 'Maio'
    	    WHEN 6 THEN 'Junho'
                WHEN 7 THEN 'Julho'
    	    WHEN 8 THEN 'Agosto'
    	    WHEN 9 THEN 'Setembro'		
    	    WHEN 10 THEN 'Outubro'
    	    WHEN 11 THEN 'Novembro'
                WHEN 12 THEN 'Dezembro'
    	 END AS 'Mes_Ano', Abertos, Fechados, 
    	 (SELECT SUM(ABERTOS) - SUM(Fechados) 
    		from CTE_BACKLOG 
    		where mes <= a.mes)   Backlog 
    	 FROM CTE_BACKLOG A
    ORDER BY A.Ano, A.Mes
    

    Resultado do script:

    Ano     Mes         Abertos    Fechados    Resultado
    2011    Janeiro        148           130       58
    2011    Fevereiro   151           150       96
    2011    Março        172           186       162
    2011    Abril        219        223       202
    2011    Maio        290           240       353
    2011    Junho        346           331       447
    2011    Julho        276           230       493
    2011    Agosto        362           305       550
    2011    Setembro    337           301       586
    2011    Outubro        260           306       540
    2011    Novembro    282           257       565
    2011    Dezembro    240           265       540
    2012    Janeiro        338           298       58
    2012    Fevereiro   391           354       96
    2012    Março        474           394       162
    2012    Abril        510           466       202
    2012    Maio        576           475       353
    2012    Junho        540           461       447

    Podemos verificar duas coisas:

    1 - O calculo não está correto, repare que abertos - fechados na primeira linha já não corresponde a resposta correta;

    2 - Quando vai do ano 2011 para 2012 o script não continua calculando e os números se repetem, ou seja, resultado de Janeiro de 2011 é igual ao de Janeiro de 2012 e assim por diante;

    Já agradeço a ajuda!

    Abraço.


    Ricardo Filho

    segunda-feira, 6 de agosto de 2012 12:57
  • Olá Ricardo,

    Bem este problema está ocorrendo por não existir um filtro do ano,

    se você analizar o resuldado da diferença de janeiro 58 correspode a 18 de janeiro de 2011 + 40 de janeiro de 2012

    Teste desta forma

    WITH CTE_BACKLOG AS (
    SELECT     
    	A.Ano, A.Mes ,A.Abertos, B.Fechados FROM
    (SELECT YEAR(IR."Data Abertura") AS 'Ano',MONTH(IR."Data Abertura") AS 'Mes', 
    		COUNT(*) AS Abertos
    FROM vw_Incidentes_Requisicoes IR
    WHERE YEAR(IR."Data Abertura") >= 2011
    GROUP BY YEAR(IR."Data Abertura"), MONTH(IR."Data Abertura")) A
    LEFT JOIN
    (SELECT YEAR(IR."Data Término Processo") AS 'Ano',MONTH(IR."Data Término Processo") AS 'Mes', 
    		COUNT(*) AS Fechados
    FROM vw_Incidentes_Requisicoes IR
    WHERE IR."Status" = 'Terminado' AND
    	  YEAR(IR."Data Término Processo") >= 2011
    GROUP BY YEAR(IR."Data Término Processo"), MONTH(IR."Data Término Processo")) B ON A.Mes = B.Mes
    WHERE A.Ano = B.Ano
    GROUP BY A.Ano, A.Mes, A.Abertos, B.Fechados)
    
    SELECT A.Ano,    
    	CASE Mes
                WHEN 1 THEN 'Janeiro'
    	    WHEN 2 THEN 'Fevereiro'
    	    WHEN 3 THEN 'Março'
    	    WHEN 4 THEN 'Abril'
    	    WHEN 5 THEN 'Maio'
    	    WHEN 6 THEN 'Junho'
                WHEN 7 THEN 'Julho'
    	    WHEN 8 THEN 'Agosto'
    	    WHEN 9 THEN 'Setembro'		
    	    WHEN 10 THEN 'Outubro'
    	    WHEN 11 THEN 'Novembro'
                WHEN 12 THEN 'Dezembro'
    	 END AS 'Mes_Ano', Abertos, Fechados, 
    	 (SELECT SUM(ABERTOS) - SUM(Fechados) 
    		from CTE_BACKLOG 
    		where mes <= a.mes
                       -- adicionada linha para o ano
                       and ano <= a.ano)   Backlog 
    	 FROM CTE_BACKLOG A
    ORDER BY A.Ano, A.Mes


    Att.
    Lukas Baldan

    segunda-feira, 6 de agosto de 2012 13:15
  • Lukas,

    Sensacional, mas agora olha o que está ocorrendo:

    2011    Janeiro       148    130    18
    2011    Fevereiro    151    150    19
    2011    Março         172    186    5
    2011    Abril           219    223    1
    2011    Maio           290    240    51
    2011    Junho         346    331    66
    2011    Julho          276    230    112
    2011    Agosto       362    305    169
    2011    Setembro   337    301    205
    2011    Outubro     260    306    159
    2011    Novembro  282    257    184
    2011    Dezembro  240    265    159
    2012    Janeiro      338    298    58   ==> A partir daqui calcula errado.
    2012    Fevereiro   391    354    96
    2012    Março        474    394    162
    2012    Abril          510    466    202
    2012    Maio          576    475    353
    2012    Junho        540    461    447

    De Janeiro de 2011 até Dezembro de 2011 ele calcula perfeitamente, quando passa para 2012 não calcula corretamente. Por que será?


    Ricardo Filho

    segunda-feira, 6 de agosto de 2012 13:28
  • Ok,

    O erro é que quando o ano for menor não se pode aplicar o filtro do mês

    tente assim

    where mes <= case when ano < A.ano THEN 12 else a.mes END
    and ano <= a.ano)   Backlog 
    


    Att.
    Lukas Baldan

    • Marcado como Resposta Ricardo.Filho segunda-feira, 6 de agosto de 2012 13:58
    segunda-feira, 6 de agosto de 2012 13:50
  • Putss Lukas, que legal, funcionou perfeitamente!!!

    Muito obrigado pela força!

    Abraço!


    Ricardo Filho

    segunda-feira, 6 de agosto de 2012 13:58
  • Lukas,

    Ainda falando do mesmo assunto, como você já está interado vou conseguir tirar esta dúvida rapidamente. Desculpa te alugar assim :s

    Eu vejo nesse script que talvez para ficar 1000% falta apenas um detalhe... Ele calcula o Backlog a partir de um determinado periodo que eu determino, ou seja, em linha geraias se eu pego o backlog de fevereiro para frente ele não calcula o backlog do passado para começar a contar de fevereiro para frente...

    2011    Maio           290    240    51

    ----------------------------------------------------

    Na teoria se eu conseguir pegar o backlog do passado somado ao backlog do mês de início fica suprasumo, neste caso o backlog do passado é de 51 e o backlog de Junho é 15, mas já calcula corretamente de junho para frente, uma vez que eu determino que o script roda de Junho para frente.

    2011    Junho         346    331    66
    2011    Julho          276    230    112
    2011    Agosto       362    305    169
    2011    Setembro   337    301    205
    2011    Outubro     260    306    159
    2011    Novembro  282    257    184
    2011    Dezembro  240    265    159

    É possível incorporar esta inteligência neste script ou teria que armazenar isso em alguma tabela temporária?


    Ricardo Filho

    segunda-feira, 6 de agosto de 2012 14:30
  • Ricardo não consegui entender muito bem,

    tem como você dar um exemplo de como seria o resultado que você espera


    Att.
    Lukas Baldan

    segunda-feira, 6 de agosto de 2012 15:53
  • Claro Lukas,

    Vamos imaginar que eu quero um relatório de Janeiro a Abril por exemplo:

    Mes - Abertos - Fechados - Backlog

    Janeiro - 100 - 90 - 10

    Fevereiro - 120 - 110 - 20

    Março - 100 - 110 - 10

    Abril - 200 - 150 - 60

    Vamos imaginar agora que eu quero tirar um relatório a partir de Maio, com o script acima o calculo de backlog é feito a partir de um determinado mes ou dia, mas o conceito de backlog não me permite considerar apenas isso, eu preciso considerar o TODO.

    Maio - 150 - 120 - 30 (Errado)

    Maio - 150 - 120 - 90 (Correto) - Como o backlog considera o TODO independente de onde vai partir a consulta eu preciso considerar o backlog do passado para começar a calcular o backlog dali para frente.

    Ou seja eu verifico qual foi o meu último registro de backlog para iniciar o calculo de um determinado período para frente.

    Espero que agora você entenda!

    Obrigado!


    Ricardo Filho

    segunda-feira, 6 de agosto de 2012 16:42
  • Ok Ricardo acho que entendi,

    Você quer mostar por exemplo a partir do mês de Maio,

    Bem acho que só colocar um filtro por ano e mês que ja vai funcionar,

    tente assim

    DECLARE @ANO INT, @MES INT SET @ANO = 2011 --ANO INICIO SET @MES = 6 --MES INICIO WITH CTE_BACKLOG AS ( SELECT A.Ano, A.Mes ,A.Abertos, B.Fechados FROM (SELECT YEAR(IR."Data Abertura") AS 'Ano',MONTH(IR."Data Abertura") AS 'Mes', COUNT(*) AS Abertos FROM vw_Incidentes_Requisicoes IR WHERE YEAR(IR."Data Abertura") >= 2011 GROUP BY YEAR(IR."Data Abertura"), MONTH(IR."Data Abertura")) A LEFT JOIN (SELECT YEAR(IR."Data Término Processo") AS 'Ano',MONTH(IR."Data Término Processo") AS 'Mes', COUNT(*) AS Fechados FROM vw_Incidentes_Requisicoes IR WHERE IR."Status" = 'Terminado' AND YEAR(IR."Data Término Processo") >= 2011 GROUP BY YEAR(IR."Data Término Processo"), MONTH(IR."Data Término Processo")) B ON A.Mes = B.Mes WHERE A.Ano = B.Ano GROUP BY A.Ano, A.Mes, A.Abertos, B.Fechados) SELECT A.Ano, CASE Mes WHEN 1 THEN 'Janeiro' WHEN 2 THEN 'Fevereiro' WHEN 3 THEN 'Março' WHEN 4 THEN 'Abril' WHEN 5 THEN 'Maio' WHEN 6 THEN 'Junho' WHEN 7 THEN 'Julho' WHEN 8 THEN 'Agosto' WHEN 9 THEN 'Setembro' WHEN 10 THEN 'Outubro' WHEN 11 THEN 'Novembro' WHEN 12 THEN 'Dezembro' END AS 'Mes_Ano', Abertos, Fechados, (SELECT SUM(ABERTOS) - SUM(Fechados) from CTE_BACKLOG where mes <= case when ano < A.ano THEN 12 else a.mes END and ano <= a.ano) Backlog FROM CTE_BACKLOG A --ADICIONADO O FILTRO POR MES E ANO WHERE A.ANO >= @ANO
    AND A
    .MES >= (CASE WHEN A.ANO > @ANO THEN 1 ELSE @MES END)

    ORDER BY A.Ano, A.Mes



    Att.
    Lukas Baldan



    segunda-feira, 6 de agosto de 2012 17:07
  • Oi lukas,

    Só está dando um erro de sintax na linha:

    AND (CASE WHEN A.ANO > @ANO THEN 1 ELSE A.MES >= @MES)

    Erro:

    Mensagem 102, Nível 15, Estado 1, Linha 46
    Sintaxe incorreta próxima a '>'.


    Ricardo Filho

    segunda-feira, 6 de agosto de 2012 17:19
  • Nossa fiz correndo fiz errado rs.

    tente assim

    WHERE A.ANO >= @ANO 
    AND A.MES >= (CASE WHEN A.ANO > @ANO THEN 1 ELSE @MES END)


    Att.
    Lukas Baldan


    segunda-feira, 6 de agosto de 2012 17:28
  • Lukas,

    Ficou nota 1000!!! Show de bola!

    Muito Obrigado!


    Ricardo Filho

    segunda-feira, 6 de agosto de 2012 17:44
  • Senhor Lukas,

    help me again please!

    Estou precisando mudar neste script para tirar um relatório por dia com dd/mm/aaaa, a idéia é a mesma, assim como um faz por mês, esse faria por dia:\

    01/05/2012 - Abertos x, fechados y, backlog xy (com o mesmo conceito do anterior), segue script:

    DECLARE @ANO INT, @MES INT

    SET @ANO = @TAno@
    SET @MES = @TMes@

    WITH CTE_BACKLOG AS (
    SELECT     
        A.Ano, A.Mes ,A.Abertos, B.Fechados FROM
    (SELECT A.Ano, A.Mes, (A.Abertos + B.Abertos) AS Abertos FROM
    (SELECT YEAR(I.DataAbertura) AS 'Ano', MONTH(I.DataAbertura) AS 'Mes',
           COUNT(*) AS Abertos
    FROM Incidentes I
    WHERE I.Status NOT IN (SELECT Id FROM Valores WHERE Nome = 'Cancelado') AND YEAR(I.DataAbertura) >= 2011
    GROUP BY YEAR(I.DataAbertura), MONTH(I.DataAbertura)) A,

    (SELECT YEAR(R.DataAbertura) AS 'Ano', MONTH(R.DataAbertura) AS 'Mes',
           COUNT(*) AS Abertos
    FROM Requisicoes R
    WHERE R.StatusRequisicao NOT IN (SELECT Id FROM Valores WHERE Nome = 'Cancelado') AND YEAR(R.DataAbertura) >= 2011
    GROUP BY YEAR(R.DataAbertura), MONTH(R.DataAbertura)) B
    WHERE A.Ano = B.Ano AND A.Mes = B.Mes) A

    LEFT JOIN

    (SELECT A.Ano, A.Mes, (A.Fechados + B.Fechados) AS Fechados FROM
    (SELECT YEAR(I.DataTerminoIncidente) AS 'Ano', MONTH(I.DataTerminoIncidente) AS 'Mes',
           COUNT(*) AS Fechados
    FROM Incidentes I
    WHERE I.Status IN (SELECT Id FROM Valores WHERE Nome = 'Terminado') AND YEAR(I.DataTerminoIncidente) >= 2011
    GROUP BY YEAR(I.DataTerminoIncidente), MONTH(I.DataTerminoIncidente)) A,

    (SELECT YEAR(R.DataTerminoRequisicao) AS 'Ano', MONTH(R.DataTerminoRequisicao) AS 'Mes',
           COUNT(*) AS Fechados
    FROM Requisicoes R
    WHERE R.StatusRequisicao IN (SELECT Id FROM Valores WHERE Nome = 'Terminado') AND YEAR(R.DataTerminoRequisicao) >= 2011
    GROUP BY YEAR(R.DataTerminoRequisicao), MONTH(R.DataTerminoRequisicao)) B
    WHERE A.Ano = B.Ano AND A.Mes = B.Mes) B ON A.Mes = B.Mes
    WHERE A.Ano = B.Ano
    GROUP BY A.Ano, A.Mes, A.Abertos, B.Fechados)

    SELECT
        CASE Mes
            WHEN 1 THEN 'Janeiro'
            WHEN 2 THEN 'Fevereiro'
            WHEN 3 THEN 'Março'
            WHEN 4 THEN 'Abril'
            WHEN 5 THEN 'Maio'
            WHEN 6 THEN 'Junho'
            WHEN 7 THEN 'Julho'
            WHEN 8 THEN 'Agosto'
            WHEN 9 THEN 'Setembro'        
            WHEN 10 THEN 'Outubro'
            WHEN 11 THEN 'Novembro'
            WHEN 12 THEN 'Dezembro'
         END AS "Mes_Ano", "Abertos", "Fechados",
         (SELECT SUM(ABERTOS) - SUM(Fechados)
            from CTE_BACKLOG
            where mes <= case when ano < A.ano THEN 12 else a.mes END
            and ano <= a.ano) "Backlog"
         FROM CTE_BACKLOG A
         WHERE A.ANO >= @ANO
         AND A.MES >= (CASE WHEN A.ANO > @ANO THEN 1 ELSE @MES END)
    ORDER BY A.Ano, A.Mes
    Imagino que a variável deve ser algo do tipor da data X até a data Y utilizando um BETWEEN por exemplo.

    Ricardo Filho

    segunda-feira, 13 de agosto de 2012 17:33
  • Eu tentei fazer conforme script abaixo, mas me perdi ao fazer o select no CTE_BACKLOG, Segue:

    DECLARE @ANO INT, @MES INT
    
    WITH CTE_BACKLOG AS (
    SELECT     
    	A.Data, A.Abertos, B.Fechados FROM
    (SELECT A.Data,(A.Abertos + B.Abertos) AS Abertos FROM 
    (SELECT CONVERT(VARCHAR(12),I.DataAbertura,103) Data,
    	   COUNT(*) AS Abertos
    FROM Incidentes I
    WHERE I.Status NOT IN (SELECT Id FROM Valores WHERE Nome = 'Cancelado') AND YEAR(I.DataAbertura) >= 2011
    GROUP BY CONVERT(VARCHAR(12),I.DataAbertura,103)) A,
    
    (SELECT CONVERT(VARCHAR(12),R.DataAbertura,103) Data,
    	   COUNT(*) AS Abertos
    FROM Requisicoes R
    WHERE R.StatusRequisicao NOT IN (SELECT Id FROM Valores WHERE Nome = 'Cancelado') AND YEAR(R.DataAbertura) >= 2011
    GROUP BY CONVERT(VARCHAR(12),R.DataAbertura,103)) B
    WHERE A.Data = B.Data) A
    
    LEFT JOIN
    
    (SELECT A.Data,(A.Fechados + B.Fechados) AS Fechados FROM 
    (SELECT CONVERT(VARCHAR(12),I.DataTerminoIncidente,103) Data,
    	    COUNT(*) AS Fechados
    FROM Incidentes I
    WHERE I.Status IN (SELECT Id FROM Valores WHERE Nome = 'Terminado') AND YEAR(I.DataTerminoIncidente) >= 2011
    GROUP BY CONVERT(VARCHAR(12),I.DataTerminoIncidente,103)) A,
    
    (SELECT CONVERT(VARCHAR(12),R.DataTerminoRequisicao,103) Data,
    	   COUNT(*) AS Fechados
    FROM Requisicoes R
    WHERE R.StatusRequisicao IN (SELECT Id FROM Valores WHERE Nome = 'Terminado') AND YEAR(R.DataTerminoRequisicao) >= 2011
    GROUP BY CONVERT(VARCHAR(12),R.DataTerminoRequisicao,103)) B) B ON A.Data = B.Data
    GROUP BY A.Data, A.Abertos, B.Fechados)
    
    SELECT 
    	 Dia, "Abertos", "Fechados", 
    	 (SELECT SUM(ABERTOS) - SUM(Fechados) 
    		FROM CTE_BACKLOG 
    			WHERE Mes <= CASE WHEN Ano < A.Ano THEN 12 ELSE A.Mes END AND Ano <= A.Ano) "Backlog"
    		FROM CTE_BACKLOG A
    			WHERE A.Ano >= @ANO AND A.Mes >= (CASE WHEN A.Ano > @ANO THEN 1 ELSE @MES END)
    ORDER BY A.Ano, A.Mes, A.Dia
    
    


    Ricardo Filho

    segunda-feira, 13 de agosto de 2012 17:34