Archive

Posts Tagged ‘T-SQL Puzzles’

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