none
Criar View RRS feed

  • Pergunta

  • Prezados veja se pode me ajudar, Preciso criar uma View para SQL abaixo.

    SELECT 
            YEAR = YEAR(dataemissao),
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
                 END), 1), '') AS 'JAN',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
                 END), 1), '') AS 'FEB',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
                 END), 1), '') AS 'MAR',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
                 END), 1), '') AS 'APR',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
                 END), 1), '') AS 'MAY',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
                 END), 1), '') AS 'JUN',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
     END), 1), '') AS 'JUL',        
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
                 END), 1), '') AS 'AUG',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
                 END), 1), '') AS 'SEP',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
                 END), 1), '') AS 'OCT',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
                 END), 1), '') AS 'NOV',
             COALESCE(CONVERT(VARCHAR,SUM(CASE
                   WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
                 END), 1), '') AS 'DEC' ,
       TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)   
    FROM    tmov
    WHERE  TMOV.CODTMV = '2.2.01'

    GROUP BY  YEAR(dataemissao)
    ORDER BY YEAR(dataemissao)
    GO

    quarta-feira, 17 de abril de 2013 13:45

Respostas

  • Ronnie,

    Basta substituir o NomeDaView, pelo nome que voce quiser.

    CREATE VIEW dbo.NomeDaVida
    AS
    SELECT  
            YEAR = YEAR(dataemissao),
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
                 END), 1), '') AS 'JAN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
                 END), 1), '') AS 'FEB', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
                 END), 1), '') AS 'MAR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
                 END), 1), '') AS 'APR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
                 END), 1), '') AS 'MAY', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
                 END), 1), '') AS 'JUN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
     END), 1), '') AS 'JUL',         
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
                 END), 1), '') AS 'AUG', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
                 END), 1), '') AS 'SEP', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
                 END), 1), '') AS 'OCT', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
                 END), 1), '') AS 'NOV', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
                 END), 1), '') AS 'DEC' ,
       TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)    
    FROM    tmov
    WHERE  TMOV.CODTMV = '2.2.01'
    GROUP BY  YEAR(dataemissao) 


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Email: fabrizzio.antoniaci@gmail.com

    quarta-feira, 17 de abril de 2013 13:48
    Moderador
  • Ronnie Von, tente assim:

    Create View [Nome da view] as
    
    SELECT  
            YEAR = YEAR(dataemissao),
              COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
                  END), 1), '') AS 'JAN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
                  END), 1), '') AS 'FEB', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
                  END), 1), '') AS 'MAR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
                  END), 1), '') AS 'APR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
                  END), 1), '') AS 'MAY', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
                  END), 1), '') AS 'JUN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
      END), 1), '') AS 'JUL',         
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
                  END), 1), '') AS 'AUG', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
                  END), 1), '') AS 'SEP', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
                  END), 1), '') AS 'OCT', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
                  END), 1), '') AS 'NOV', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
                  END), 1), '') AS 'DEC' ,
        TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)   
     FROM    tmov
     WHERE  TMOV.CODTMV = '2.2.01'
     
    GROUP BY  YEAR(dataemissao) 
    


    Alexandre Matayosi Conde Mauricio.

    quarta-feira, 17 de abril de 2013 13:49

Todas as Respostas

  • Ronnie,

    Basta substituir o NomeDaView, pelo nome que voce quiser.

    CREATE VIEW dbo.NomeDaVida
    AS
    SELECT  
            YEAR = YEAR(dataemissao),
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
                 END), 1), '') AS 'JAN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
                 END), 1), '') AS 'FEB', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
                 END), 1), '') AS 'MAR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
                 END), 1), '') AS 'APR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
                 END), 1), '') AS 'MAY', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
                 END), 1), '') AS 'JUN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
     END), 1), '') AS 'JUL',         
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
                 END), 1), '') AS 'AUG', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
                 END), 1), '') AS 'SEP', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
                 END), 1), '') AS 'OCT', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
                 END), 1), '') AS 'NOV', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
                 END), 1), '') AS 'DEC' ,
       TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)    
    FROM    tmov
    WHERE  TMOV.CODTMV = '2.2.01'
    GROUP BY  YEAR(dataemissao) 


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Email: fabrizzio.antoniaci@gmail.com

    quarta-feira, 17 de abril de 2013 13:48
    Moderador
  • Ronnie Von, tente assim:

    Create View [Nome da view] as
    
    SELECT  
            YEAR = YEAR(dataemissao),
              COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 1 THEN VALORBRUTO
                  END), 1), '') AS 'JAN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 2 THEN VALORBRUTO
                  END), 1), '') AS 'FEB', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 3 THEN VALORBRUTO
                  END), 1), '') AS 'MAR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 4 THEN VALORBRUTO
                  END), 1), '') AS 'APR', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 5 THEN VALORBRUTO
                  END), 1), '') AS 'MAY', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 6 THEN VALORBRUTO
                  END), 1), '') AS 'JUN', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 7 THEN VALORBRUTO
      END), 1), '') AS 'JUL',         
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 8 THEN VALORBRUTO
                  END), 1), '') AS 'AUG', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 9 THEN VALORBRUTO
                  END), 1), '') AS 'SEP', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 10 THEN VALORBRUTO
                  END), 1), '') AS 'OCT', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 11 THEN VALORBRUTO
                  END), 1), '') AS 'NOV', 
             COALESCE(CONVERT(VARCHAR,SUM(CASE 
                   WHEN MONTH(dataemissao) = 12 THEN VALORBRUTO
                  END), 1), '') AS 'DEC' ,
        TOTAL_ANO = Sum(Case When (Year(dataemissao) = Year(GetDate()) ) Then IsNull(VALORBRUTO, 0) End)   
     FROM    tmov
     WHERE  TMOV.CODTMV = '2.2.01'
     
    GROUP BY  YEAR(dataemissao) 
    


    Alexandre Matayosi Conde Mauricio.

    quarta-feira, 17 de abril de 2013 13:49
  • valeu galera OK.

    quarta-feira, 17 de abril de 2013 17:03