Archive

Archive for the ‘Fundamentos T – SQL’ Category

Desafio T-SQL

Ola pessoal, tudo bem?

Essa semana presenciei um caso aonde era necessário ajudar a desenvolver uma instrução T-SQL que fugia um pouco do nosso dia-a-dia, devido a isso resolvir postar no blog um desafio para compartilhar com todos a solução depois. Espero que muitas pessoas participem.

Vamos ao cenário.

Imagine que o Sistema X realiza o “track” de contratos de uma instituição financeira que são gerados pelo sistema Y, no entanto,o sistema Y gera mais de UMA LINHA para cada contrato toda vez que esse contrato é renegociado. Isso significa que, cada conjunto de contrato no sistema Y terá mais de uma linha baseando-se no número de contrato mais codigo de movimentação do mesmo (Contrato + Mov). Vejam um sample dos dados, conforme a seguir:

O sistema X precisa transformar o resulte set da imagem anterior em um único registro levando as seguintes regras em consideração:

  1. O status 0 significa – Precisa ser avaliado. O Status 1 significa: Registro Válido e o Status 2 significa que o registro pode ser descartado.
  2. Se o os valores em Dt1 e Dt2 for NULOS para para um conjunto de registro (Contrato + Processo) utilizo o registro com a MENOR DtReal.
    1. Obs: O print não atende essa condição, mas, consideraria asegunda linha da minha tabela:

       

       

  3. Se algum dos valores em Dt1 e Dt2 não for NULO para um conjunto de registro (Contrato + Processo) utilizo o registro com a MAIOR DtReal como base.
  4. Quando a regra de número 3 for atendida é necessário “montar” o registro com a seguinte “sub-regra”:
    1. Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) for maior ou igual a minha DtReal da linha atual, considero o MAX (no caso o valor 25/08/2013) para essa coluna. (Nesse caso minha coluna DtReal seria 22/08/2013 + Dt1 = 25/08/2013). No entanto, temos outra “regrinha” que no caso a regra “4.b”.
    2. Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) for maior ou igual ao MAX VALUE da DtReal do conjunto de registro (Contrato + Processo) o valor da coluna DtReal DEVE ser a menor data.O registro deve ser como a seguir:

       

  5. Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) NÃO for maior que a minha linha BASE (no caso a DtReal=22/08/2013, seguindo a regra de número 3) , conforme print:

    Nesse caso minha linha deve ser montada da seguinte maneira:

  6. Minha última regra consiste no seguinte: Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) NÃO for maior que a minha linha BASE (no caso a DtReal=22/08/2013, seguindo a regra de número 3) E o valor da Coluna Dt1 da linha BASE (no caso a linha que possi o valor DtReal = 22/08/2013) NÃO for NULA, minha linha consiste na DtReal + Dt1 daquela linha. Veja o cenário:

     

    Para a regra de número 6 o meu registro deve ser algo mais o menos assim:

     

     

Observações:

  • Os registros que devem aparecer na solução no final devem ter o valor da coluna Status modificado para 1
  • A solução pode ser escrita nas seguintes versões do SQL Server: 2008,200R2, 2012 e 2014.
  • A solução pode ser feita em mais de uma consulta para atender as regras.
  • Não é permitido a ulização de abordagens linha-a-linha (While e CUSRSOR)

Estou disponibilizando o script que monta os cenários com todos os registros. A ideeia inicial é desenvolver um pouco a lógica e fazer exercícios de T-SQL. No futuro podemos utilizar o mesmo cenário e avaliar o desempenho das sugestões que serão enviadas e discutir todas elas.

use tempdb

go

–Create sample datas

CREATE
TABLE #T
(Contrato VARCHAR(50), Mov INT, DtReal date, Dt1 date, Dt2 date,[Status] tinyint)

–DROP TABLE #T

INSERT
INTO #T(Contrato, Mov,DtReal,Dt1,Dt2, [Status])


VALUES


(’00XXXXX.25.0000′, 80193, ‘20130701’,
NULL        ,NULL,0)


,(’00XXXXX.25.0000′, 80193, ‘20130617’,
NULL        ,NULL,0)


,(’00XXXXX.25.0000′, 80193, ‘20130821’, ‘20130825’    ,NULL,0)


,(’00XXXXX.25.0000′, 80193, ‘20130822’,
NULL        ,NULL,0)

 

            ,(’00YYYYY.26.0000′, 80192, ‘20130701’, ‘20130820’    ,NULL,0)


,(’00YYYYY.26.0000′, 80192, ‘20130617’,
NULL        ,NULL,0)


,(’00YYYYY.26.0000′, 80192, ‘20130821’,
NULL        ,NULL,0)


,(’00YYYYY.26.0000′, 80192, ‘20130822’,
NULL        ,NULL,0)

 


,(’00ZZZZZ.27.0000′, 80194, ‘20130701’,
NULL        ,NULL,0)


,(’00ZZZZZ.27.0000′, 80194, ‘20130617’, ‘20130825’    ,NULL,0)


,(’00ZZZZZ.27.0000′, 80194, ‘20130821’,
NULL        ,NULL,0)


,(’00ZZZZZ.27.0000′, 80194, ‘20130822’, ‘20130821’    ,NULL,0)

 


,(’00WWWWW.28.0000′, 80195, ‘20130701’,
NULL        ,NULL,0)


,(’00WWWWW.28.0000′, 80195, ‘20130617’, ‘20130820’    ,NULL,0)


,(’00WWWWW.28.0000′, 80195, ‘20130821’,
NULL        ,NULL,0)


,(’00WWWWW.28.0000′, 80195, ‘20130822’, ‘20130821’    ,NULL,0)

 

            ,(’00KKKKK.29.0000′, 80196, ‘20130701’,
NULL        ,NULL,0)


,(’00KKKKK.29.0000′, 80196, ‘20130617’,
NULL        ,NULL,0)


,(’00KKKKK.29.0000′, 80196, ‘20130821’,
NULL        ,NULL,0)


,(’00KKKKK.29.0000′, 80196, ‘20130822’,
NULL        ,NULL,0)

 

            ,(’00AAAAA.30.0000′, 80196, ‘20130701’,
NULL        ,NULL,0)


,(’00AAAAA.30.0000′, 80196, ‘20130602’,
NULL        ,NULL,0)


,(’00AAAAA.30.0000′, 80196, ‘20130821’,
NULL        ,NULL,0)


,(’00AAAAA.30.0000′, 80196, ‘20130822’,
NULL        ,NULL,0)

 

            ,(’00BBBBB.19.0000′, 80196, ‘20130701’,
NULL        ,NULL,0)


,(’00BBBBB.19.0000′, 80196, ‘20130502’,
NULL        ,NULL,0)


,(’00BBBBB.19.0000′, 80196, ‘20130821’,
NULL        ,NULL,0)


,(’00BBBBB.19.0000′, 80196, ‘20130822’,
NULL        ,NULL,0)

 

Aguardo o script com a solução por e-mail. Fiquem a vontade para discutirmos. O Resultado da consulta deve ser o da imagem abaixo:

Thiago Carlos [TC] de Alencar

Advertisements

Clausula TOP em Subqueries

Ola Galera! Vou postar uma coisa que hoje estava avaliando junto a um amigo desenvolvedor aqui na nossa fábrica. Marcos Meskelis…Um dos melhores que ja conheci…dono de uma super lógica. Já me ajudou a sair de várias, mas, vamos aos fatos e ao SQL Server (a melhor parte). Tinhamos uma query que criava uma derived table e que dentro dela havia duas outras quries simples…por um tempo que conversavamos sobre o “Brasileirão” e tal… Veio a pergunta! O que o SQL Server faz se dentro da subquery uma das consultas tiver uma clausula TOP e a consulta “externa” que gera a Derived Table também tiver uma Clausula TOP que retorna uma menor quantidade de linhas da Clausula TOP interna? Será que o SQL Server irá ler as duas tabelas da subquery pra aplicar o filtro no TOP da query externa no output das  subqueries? A melhor resposta que tivemos de imediato foi. Vamos Testar!  Crio duas tabelas uma de cliente e uma outra tabela de fornecedor. Na tabela TB_CLIENTE e na tabela TB_FORNECEDOR e realizo a inserção de seis registros para cada.

use tempdb
go
IF OBJECT_ID(‘dbo.TB_CLIENTE’) IS NOT NULL
DROP TABLE dbo.TB_CLIENTE ;
IF OBJECT_ID(‘dbo.TB_FORNECEDOR’) IS NOT NULL
DROP TABLE dbo.TB_FORNECEDOR ;

CREATE TABLE dbo.TB_CLIENTE
(
COD INT IDENTITY
,NOME VARCHAR(50)
,CPF CHAR(20)
,RG CHAR(25)
CONSTRAINT PK_TB_CLIENTE PRIMARY KEY CLUSTERED(COD)
)

CREATE TABLE dbo.TB_FORNECEDOR
(
COD INT IDENTITY
,RAZAO_SOCIAL VARCHAR(50)
,NOME_FANTASIA VARCHAR(80)
,CNPJ CHAR(20)
CONSTRAINT PK_TB_FORNECEDOR PRIMARY KEY CLUSTERED(COD)
)

INSERT INTO dbo.TB_CLIENTE(NOME,CPF,RG) 
VALUES (‘THIAGO ALENCAR’,’89636978925′,’78452362X’) 
,(‘EMANOEL ALENCAR’,’79636978925′,’88452362X’) 
,(‘CAIO LACRAIA’,’99636978925′,’784523625′) ;

INSERT INTO dbo.TB_CLIENTE(NOME,CPF,RG) 
VALUES (‘ALENCAR’,’89636978925′,’78452362X’) 
,(‘EMANOEL ‘,’79636978925′,’88452362X’) 
,(‘CAIO ‘,’99636978925′,’784523625’) ;
INSERT INTO dbo.TB_FORNECEDOR(RAZAO_SOCIAL,NOME_FANTASIA,CNPJ)
VALUES (‘D2 DA SILVA’,’MISIFIO LTDA’,’01.222.333/0001-12′)
,(‘KARATE / ARTES ‘,’JULIAO LTDA’,’01.222.333/0001-00′)
,(‘T. BUENO LTDA’,’TB GUARULHOS’,’01.222.333/0001-00′)
INSERT INTO dbo.TB_FORNECEDOR(RAZAO_SOCIAL,NOME_FANTASIA,CNPJ)
VALUES (‘D2′,’MISIFIO LTDA’,’01.222.333/0001-12′)
,(‘ARTES ‘,’JULIAO LTDA’,’01.222.333/0001-00′)
,(‘T. BUENO LTDA’,’GUARULHOS’,’01.222.333/0001-00′)

Vamos testar…rs! Habilite o plano de execução (Ctrl+M) e execute a query abaixo:

SET STATISTICS PROFILE ON
SELECT 
TOP 6 * 
FROM 
(
SELECT  COD, NOME,CPF FROM dbo.TB_CLIENTE 
UNION ALL
SELECT COD, NOME_FANTASIA,CNPJ FROM DBO.TB_FORNECEDOR 
) AS A
OPTION(RECOMPILE)
SET STATISTICS PROFILE OFF
A tabela de TB_CLIENTE contém 6 registros, assim como a de fornecedor. O SQL Server realiza um clustered index scan nas duas tabelas, porém, não é retornado nenhum registro da tabela de fornecedor. Pois o SQL Server  sabe que existem 6 registros na tabela de cliente e o TOP da consulta “externa” solicita o retorno de 6 registros.  Esse comportamento pode ser visto no plano de execução.
Obs: O SQL Server é completamente inteligente para não realizar a leitura em outro indice de uma  outra tabela. Já que o resultado do TOP quer apenas 6 registros. Vale lembrar que esse é apenas  um cenário para ver como as coisas funionam e qual caminho o SQL Server optou usar. Quando o TOP  é maior que 100 o SQL Server realiza um outro algoritmo. Isso pode mudar o plano de execução
 apresentado aqui.

Concatenando Valores na Mesma Linha – T-SQL

Galera, boa tarde. Este post é dedicado ao meu amigo Caio Vinicius. Ele só tem um problema: Programa em Java…rs.Mais o mano é firmeza. Vamos ao post! Colocando a mão na massa.

Estou trazendo hoje uma solução que apliquei em uma procedure hoje aqui na empresa. “Teoricamente” o problema poderia ser resolvido por um loop. Essa foi a primeira idéia do desenvolvedor,mas , pedi para ele que tivesse paciência para ambos pensarmos em uma solução em lote(Além de performática é mais elegante). Uma vez li que: Qualquer instrução que é feito linha-a-linha pode ser modificada para uma instrução “set-based” By Itzik.

Vamos Ao Cenário:
Em um ambiente OLTP, você tem uma tabela PAI e uma tabela FILHO em um relacionamento “1:N”. Um pai pode ter um ou mais filhos. O print abaixo mostra a estrutura das tabelas.

Como pode ser visto a Id 1(“X”) é Pai dos registros 1,2 e 3(A,B e C) na tabela filho (Contorno preto). E o Id 2 (y) é pai dos registros 4 e 5 (D e E) na tabela filho(Contorno Vermelho).

Um amigo desenvolvedor gostaria de realizar uma query deveria trazer o registro Pai em uma linha juntamente com o nome dos filhos concatenados na mesma linha. O resultado seria algo mais o menos assim:


Sabiamos que para não realizar um loop, poderíamos usar CTEs para resolver nosso problema, então fomos navegar na net até que achamos os caminhos das pedras (Referências no final do artigo).

Vamos ao código:
Primeiramente crio as tabelas #pai e #filho com os registros que foram ilustrados no print das estruturas da tabela.

Use tempdb
go

create table #pai(id int, descricao varchar(50))
create table #filho(id int, descricao varchar(50),id_pai int)

insert into #pai(id,descricao)values(1,’X’),(2,’Y’)
insert into #filho(id,descricao,id_pai)values(1,’A’,1),(2,’B’,1),(3,’C’,1)
insert into #filho(id,descricao,id_pai)values(4,’D’,2),(5,’E’,2)

Neste exeplo foram criadas três CTEs para resolver o problema.

;WITH RangeTabelaFilho ( PaiID, rnk, Filho )
AS (
      SELECT    f.id_pai as IdPai,
                ROW_NUMBER() OVER(PARTITION BY f.id_pai ORDER BY f.id_pai ) as IncrementoPorIdPai,
                CAST(f.descricao AS VARCHAR(8000))
         FROM #filho f
         join #pai p
         on f.id_pai = p.id
    )

Dentro desta CTE é feito um select da tabela #filho e da tabela #pai. A função ROW_NUMBER com a claúsula  Partition By diz ao SQL Server o seguinte: Cria um valor incremental inteiro e quando o idPai “mudar” recomeça a contagem. Esse comportamento pode ser visto nas linhas contornadas do print anterior.

2º – A segunda CTE chamada “AncoraDoRank” faz uma consulta na primeira CTE apenas filtrando os membros ancoras que são os registros com o rnk = 1. Perceba que as ancoras da consulta são os registros contornados no
primeiro print:

,AncoraDoRank (PaiID, rnk, Filho)
AS
(
            SELECT PaiID,rnk,Filho
            FROM RangeTabelaFilho        WHERE rnk = 1
)

3º – A terceira CTE chamada “RecursoRanqueamento” é uma CTE recursiva que é feita entre as duas primeiras CTEs já citadas anteriormente. A CTE recursiva usa a CTE “AncoraDoRank” para pegar os membros ancoras. E faz a recursividade com a CTE “RangeTabelaFilho” e a própria “RecursoRanqueamento”. O resultado é o seguinte:

,RecursoRanqueamento (PaiID, rnk, Filho)
AS
(

        SELECT PaiID , rnk, Filho
        FROM AncoraDoRank
        UNION ALL
        SELECT    RangeTabelaFilho.PaiID, RangeTabelaFilho.rnk,
                        RecursoRanqueamento.Filho + ‘, ‘ + RangeTabelaFilho.Filho
                  FROM RangeTabelaFilho
            INNER JOIN RecursoRanqueamento
            ON RangeTabelaFilho.PaiID = RecursoRanqueamento.PaiID
            AND RangeTabelaFilho.rnk = RecursoRanqueamento.rnk + 1
 )

Perceba que foram retornados cinco registros. Para cada “nivel” de rank o SQL Server criou a concatenação para cada leitura que ele passou dentro da recursividade.

Ex: Rank 1,2 e 3 do IdPai de numero 1,o resultado foi: Rnk 1 = A, Rnk 2 = A,B e Rnk 3 = A,B,C.
No final apenas faço um SELECT na CTE trazendo PaiID e realizando um MAX na coluna nome dos Filho, para que o resultado seja apenas duas linhas (PaiID 1 e 2). O resultado atende a necessidade anterior sem que seja necessário
o uso de um loop, cursor.  Abaixo Segue o script geral da solução:

Use tempdb
go

create table #pai(id int, descricao varchar(50))
create table #filho(id int, descricao varchar(50),id_pai int)

insert into #pai(id,descricao)values(1,’X’),(2,’Y’)
insert into #filho(id,descricao,id_pai)values(1,’A’,1),(2,’B’,1),(3,’C’,1)
insert into #filho(id,descricao,id_pai)values(4,’D’,2),(5,’E’,2)

;WITH RangeTabelaFilho ( PaiID, rnk, Filho )
AS (
      SELECT    f.id_pai as IdPai,
                ROW_NUMBER() OVER(PARTITION BY f.id_pai ORDER BY f.id_pai ) as IncrementoPorIdPai,
                CAST(f.descricao AS VARCHAR(8000))
         FROM #filho f
         join #pai p
         on f.id_pai = p.id
    )
,AncoraDoRank (PaiID, rnk, Filho)
AS
(
            SELECT PaiID,rnk,Filho
            FROM RangeTabelaFilho        WHERE rnk = 1
)
,RecursoRanqueamento (PaiID, rnk, Filho)
AS
(

        SELECT PaiID , rnk, Filho
        FROM AncoraDoRank
        UNION ALL
        SELECT    RangeTabelaFilho.PaiID, RangeTabelaFilho.rnk,
                        RecursoRanqueamento.Filho + ‘, ‘ + RangeTabelaFilho.Filho
                  FROM RangeTabelaFilho
            INNER JOIN RecursoRanqueamento
            ON RangeTabelaFilho.PaiID = RecursoRanqueamento.PaiID
            AND RangeTabelaFilho.rnk = RecursoRanqueamento.rnk + 1
 )

SELECT PaiID, MAX( Filho ) AS NomeFilhos FROM RecursoRanqueamento  GROUP BY PaiID;
DROP TABLE #filho
DROP TABLE #pai

Espero ter ajudado.

Referências:
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79050
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Retorna dados Aleatórios

Ola galera, blz ?
Hoje irei postar uma dica bem rápida aqui. Nesta manhã um desenvolvedor me perguntou:  Thiago como você faria para
criar uma query usando um top 10 que sempre retorne valores aleatórios?
Suspense no AR ………….EURECA!
Se desejo ordenar os valores em ordem aleatórios,  a coluna pela qual devo criar um order by deve ser uma coluna que modifique a toda execução. Isso! Usei a coluna  NEW_ID() para ordernar, vamos ao Exemplo:

USE AdventureWorks
go
SELECT TOP 10 *
    FROM AdventureWorks.Sales.SalesOrderDetail
    ORDER BY NEWID()

Calculo SQL Server com Windows Function

Ola galera, bom dia(Agora já é tarde, tive que parar pra resolver umas “buchinhas”, voltando após almoço e globo esporte). Blz ?
Hoje iremos fazer uma query usando as windows Function do SQL Server.Esse post  foi originado de uma query que tivemos que criar aqui na empresa. E a maneira mais “elegante” de escrever a  query foi usando as Windows Function.
Tendo as seguintes informações em um arquivo .xls, que me foi passado por um amigo desenvolvedor:
TabelaA

 

TabelaB

E o mesmo deseja o seguinte resultado as informações:

 

Vou explicar os calculos:

primeira Coluna “SomaProdutos” é a soma dos produtos das duas tabelas por IdPeriodo
Ex: A tabela A e a Tabela B armazena a informação de Valor para o periodo  1.
A soma dos valores 77 + 55 é  igual a 132 e assim sucessivamente.
A coluna “DiasUteis” é uma informação que esta atrelada a cada IdPeriodo. Em um periodo existe uma
certa quantidade de dias uteis para aquele periodos. No meu caso trasnformei essas    informações em
uma tabela de dias uteis e o IdPeriodo.
A coluna “Multiplicação” é a multiplicação dos DiasUteis por SomaProdutos.
A coluna “SomaMultiplicacao” é a soma total da coluna “Multiplicacao”.
A coluna “SomaDias” é a soma da coluna DiasUteis.
E a ultima coluna “Resultado” é a Divisão da coluna  “SomaMultiplicacao” por “SomaDias”.

Uma das vantagens da Windows Function é que podemos ter acesso aos detalhes de uma informação agrupada.
Mais informações Fabiano Amorim: http://blogs.solidq.com/fabianosqlserver/Post.aspx?ID=58

Vamos aos código (A parte boa….rs).
Primeiro crio as tabelas temporarias conforme arquivo .xls (É possivel fazer apenas com uma tabela de periodo,mas, depois da fejuca. Vamos que vamos). Crio a tabela de dias uteis e insiro as informações do meu arquivo .xls.
use tempdb
go
create table #tmp_produto_um(id_periodo int, id_produto int, valor decimal(19,2))
create table #tmp_produto_dois(id_periodo int, id_produto int, valor decimal(19,2))
create table #tmp_dias_uteis(id_periodo int, qtdeDias int)

insert into #tmp_produto_um(id_periodo,id_produto,valor) values(1,18,55.00),(2,18,44.00),(3,18,33.00),(4,18,22.00)
insert into #tmp_produto_dois(id_periodo,id_produto,valor) values(1,19,77.00),(2,19,66.00),(3,19,44.00),(4,19,33.00)
insert into #tmp_dias_uteis(id_periodo,qtdeDias)values(1,22),(2,21),(3,22),(4,23)

SELECT * FROM #tmp_produto_um
SELECT * FROM #tmp_produto_dois
SELECT * FROM #tmp_dias_uteis

 

 

 

Crio uma CTE com a soma dos Valores que no .xls é: SomaProdutos.A multiplicação que no .xls é:DiasUteis * SomaProdutos.

;WITH cte AS
(
SELECT    p.id_periodo                                AS IdPeriodo
        ,SUM(d.valor) + SUM(p.valor)                 AS ValorTotal
        ,u.qtdeDias                                    AS QtdeDias
        ,(SUM(d.valor) + SUM(p.valor)) * u.qtdeDias    AS ProdutVsDiasUteis
        FROM #tmp_produto_um p
        JOIN #tmp_produto_dois d
        ON p.id_periodo = d.id_periodo
        JOIN #tmp_dias_uteis u
        ON u.id_periodo = p.id_periodo
        GROUP BY p.id_periodo,u.qtdeDias
)
Abaixo realizo a query chamando a CTE e usando o SUM com o OVER() para que possa ser realizado a soma das colunas, sem a necessidade de agregação. E dentro da SubQuery crio a soma para ser usada como a coluna resultado. Código total abaixo:

;WITH cte AS
(
SELECT    p.id_periodo                                AS IdPeriodo
        ,SUM(d.valor) + SUM(p.valor)                 AS ValorTotal
        ,u.qtdeDias                                    AS QtdeDias
        ,(SUM(d.valor) + SUM(p.valor)) * u.qtdeDias    AS ProdutVsDiasUteis
        FROM #tmp_produto_um p
        JOIN #tmp_produto_dois d
        ON p.id_periodo = d.id_periodo
        JOIN #tmp_dias_uteis u
        ON u.id_periodo = p.id_periodo
        GROUP BY p.id_periodo,u.qtdeDias
)
SELECT  
        c.IdPeriodo,
        c.ValorTotal,
        c.QtdeDias,
        c.ProdutVsDiasUteis
        ,SUM(c.ProdutVsDiasUteis)        OVER()    AS SomaProdutVsDiasUteis
        ,SUM(c.QtdeDias)                OVER()    AS SomaTotalDias
        ,ROUND(D.SomaDiasUteis / D.SomaDias,2)  AS GeralCalculado
        FROM cte c
        JOIN (    SELECT    a.IdPeriodo
                        ,SUM(a.ProdutVsDiasUteis)        OVER()    AS SomaDiasUteis
                        ,SUM(a.QtdeDias)                OVER()    AS SomaDias
                FROM  cte a
                ) as D
        ON c.IdPeriodo = D.IdPeriodo

Como pode ser visto o resultado do SQLServer é o mesmo que o arquivo .xls que foi colocada no print anteriormente.
Resultado SQL Server:


Espero ter ajudado.
Abs a todos…e tenham uma ótima semana.


Funções de Ranking

No SQL Server 2005 apareceram as funes de ranqueamento. Essas funções nos ajudam a realizar algumas tarefas com mais facilidade que anteriormente(SQL 2000) .Você pode criar um numero incremental em uma consulta(simular a propriedade identity) ,criar funcionalidade de paginação e etc. Aqui mostrarei alguns exemplos onde essas funções possam ser aplicadas: A função ROW_NUMBER() acrescenta um inteiro incremental em uma consulta. Imagine que você precise montar uma consulta com o valor incremental e a nossa tabela não existe uma coluna identity(auto incremento), você pode usar a função ROW_NUMBER() para simular esse comportamento.

use tempdb
GO
if OBJECT_ID(‘dbo.tb_incrementa’,’U’)isnot null
drop table   dbo.tb_incrementa ;
create table dbo.tb_incrementa(cliente intnotnull,nome varchar(30)notnull)
INSERT INTO dbo.tb_incrementa(cliente,nome)
VALUES (10,’Kalen Delaney’),(15,’Paul Randal’),
(16,’Kimberly L. Tripp’),(19,’Itzik Ben Gan’),
(20,’Bill Gates’),(23,’Pinal’)

O select traz as informações dos clientes que tem código e nome,  o codigo do mesmo no incremental se nós quisessemos realizar uma consulta dando o valor incremental para os valores usaremos o ROW_NUMBER().

SELECT *  FROM dbo.tb_incrementa


Com a query abaixo possível criar um valor incremental para cada registro:
SELECT nome ASNomeCliente , ROW_NUMBER()OVER(ORDERBY cliente ASC)AS ValorInrementalFROM dbo.tb_incrementa:

Usando RANK e DENSE_RANK
Abaixo crio uma tabela chamada tb_corrida que armazena o nome de alguns pilotos e o tempo que os mesmos realizaram asprovas em uma competição.

if OBJECT_ID(‘dbo.tb_corrida’,’U’)is not  null
drop  table dbo.tb_corrida ;
create table dbo.tb_corrida
(idCorrida  intidentitynotnull, nomeCorredor varchar(30)notnull,
dataCorrida datenotnull, tempoCorrida timenotnull )

INSERT INTO dbo.tb_corrida(nomeCorredor,dataCorrida,tempoCorrida)
VALUES (‘Sebastian Vettel’,CURRENT_TIMESTAMP,’01:35:12:333′ ),
(‘Lewis Hamilton’,CURRENT_TIMESTAMP,’01:45:01:123′),
(‘Mark Webber’,CURRENT_TIMESTAMP,’01:45:00:128′),
(‘Felipe Massa’,CURRENT_TIMESTAMP,’01:59:15:123′),
(‘Rubens Barrichello’,CURRENT_TIMESTAMP,’01:59:15:123′),
(‘Vitaly Petrov’,CURRENT_TIMESTAMP,’02:15:16′),
(‘Sebastian Buemi’,CURRENT_TIMESTAMP,’02:15:16′),
(‘Jenson Button’,CURRENT_TIMESTAMP,’02:30:00′),
(‘Fernando Alonso’,CURRENT_TIMESTAMP,’02:31:56′)

Imagine que precisaremos criar uma query com o ranking de chegada dos corredores baseando-se no menor tempo de corrida poderiamos usar a função RANK() ou DENSE_RANK(). A única diferença entre elas que a RANK deixa um “buraco” para as informações que tem o mesmo valor. Você pode observar que Felipe Massa e Rubinho tem o mesmo tempo de conclusão da prova, fazendo os dois ficarem na mesma posição. A próxima classifição que deveria ser o “5” foi ignorado para o SQL Server. Quando a função DENSE_RANK() é utilizada esse “gap” desaparece. Como pode ser visto o SQL Server realiza o Rank em cima do tempo de concluso da prova.

SELECT nomeCorredor AS NomePiloto, tempoCorrida AS Tempo, RANK()OVER(ORDERBY tempoCorrida ASC)AS PosicaoRank, DENSE_RANK()OVER(ORDERBY tempoCorrida ASC)AS PosicaoDenseRank FROM dbo.tb_corrida

Um outro exemplo que pode ser feito montar o rank mundial dos corredores tendo como  parâmetro o número de vitórias/pontos do corredor. O corredor que tiver mais vitórias/ponto,  ser “top” no pódio. Esse exemplo também poderia também ser aplicado para criar a  tabela do campeonato brasileiro por exemplo. Abaixo crio a tabela tb_podio e populo com algumas informações ficticias.

if OBJECT_ID(‘dbo.tb_podio’,’U’)isnotnull
droptable dbo.tb_podio ;
createtable dbo.tb_podio(
idCorrida intidentitynotnull,
nomePiloto varchar(30)notnull,
Pais varchar(15)notnull,
totalPontos int,
Equipe varchar(40),
numeroVitorias int )
INSERT INTO dbo.tb_podio(nomePiloto,Pais,totalPontos,Equipe,numeroVitorias)
VALUES (‘Sebastian Vettel’,’ALE’,148,’RBR-Renault’, 4),
(‘Lewis Hamilton’,’ING’,85,’McLaren-Mercedes’,1),
(‘Mark Webber’,’AUS’,79,’RBR-Renault’,0),
(‘Rubens Barrichello’,’BRA’,2,’Williams-Cosworth’,0),
(‘Vitaly Petrov’,’ING’,21,’Renault-Lotus’,0),
(‘Sebastian Buemi’,’SUI’,7,’STR-Ferrari’,0),
(‘Jenson Button’,’ING’,76,’McLaren-Mercedes’,0),
(‘Fernando Alonso’,’ESP’,69,’Ferrari’,0 )

Abaixo a query e feita pelo o total de pontos acumulados pelos corredores. Montando o rank mundial por pontos.A query também poderia ser feita pelo numero de vitórias.

SELECT Equipe,nomePiloto as Piloto, numeroVitorias,TotalPontos ,
DENSE_RANK()OVER(ORDERBY totalPontos DESC)As Classificacao
FROM dbo.tb_podioORDERBYClassificacao

Usando o NTILE
O NTILE() usado para dividir um conjunto de resultados em grupos aproximadamente iguais. Imagine que você tem uma tabela de pedidos com o valor total dos pedidos. O departamento de negócios da empresa solicitou que você criasse uma query que trouxesse os produtos em grupo por valor.
O NTILE() recebe um inteiro como parâmetro, esse numero é usado para quebrar o total de registros em grupos. Quando o total de registros é divido em grupos pelo valor do parâmetros de entrada  do NTILE() o SQL Server incrementa a quantidade de registros da divisao(que sobraram) para o primeiros grupos. Por exemplo: Se temos 32 registros e dividimos ele em 3 grupos, o SQL Server ir criar 3 grupos com 10 registros. Com os dois registros restantes o SQL Server ir colocar um no primeiro grupo e outro no segundo. A query abaixo cria tr categorias para os produtos tendo como base o valor dos mesmos, so elas: Baixo, Medio e Alto.

if OBJECT_ID(‘dbo.tb_produtos’) is not null
 drop table dbo.tb_produtos ;
create table dbo.tb_produtos
(
codigo int identity not null,
descricao varchar(50) not null,
valor money not null
)
GO
INSERT INTO dbo.tb_produtos(descricao,valor)
VALUES (‘Lapis’,1.00), (‘Caneta’,1.50),(‘Celular’,130.00)
  ,(‘Livros PMO’,175.00), (‘Monitor’,450.00),(‘TV’,1200.00)
  ,(‘DVD’,450.00),(‘MP4’,250.00),(‘New Civic’,60000.00)
  ,(‘NoteBook’,2500.00),(‘Fogao’,176.00)
go
SELECT
  descricao AS Produto,
  valor  AS Valor,
 CASE NTILE(3) OVER(ORDER BY valor)
    WHEN 1 THEN ‘Baixo’
    WHEN 2 THEN ‘Médio’
    WHEN 3 THEN ‘Alto’
    ELSE ‘Sem Categoria’
 END AS DescricaoTitulo
FROM dbo.tb_produtos
ORDER BY valor;

Abs Galera. Por enquanto é só.