Short Scripts – Setembro 2019

Olá bom dia, como vai?

Seja bem-vindo, em mais um post da sessão Short Scripts, estamos no mês de setembro, 2019 está começando a apontar para sua reta final, olha vou te dizer uma coisa, “como o tempo passa, não é mesmo (kkkkk)”.

Deixando o tempo de lado, caminhando para mais um dia de muito trabalho e estudos, que hora poder encontra-lo aqui meu dileto visitante, no post de número 38 desta tradicional sessão.

Mantendo a tradição estou retornando com mais um conjunto dos “curtos ou pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, vou pagar a minha hoje, feita no encerramento do post anterior publicado no mês de junho, se por acaso você ainda não acessou o mesmo, calma, fique tranquilo, tudo vai dar certo, no encerramento deste post você terá a sua disposição a relação das últimas cinco publicações relacionadas aos Short Scripts.

Vamos caminhar mais um pouco então……..

O post de hoje

De forma costumeira e conhecida por você que está sempre me visitando, selecionei os principais scripts armazenados nos últimos 90 dias em minha biblioteca de códigos, os quais apresentam os seguintes assuntos:

  • Agrupamento de Dados;
  • Case When;
  • Cláusula Over();
  • Comando Partition By;
  • DataType Float;
  • DataType TinyInt;
  • DMV sys.dm_exec_sessions;
  • Função DateAdd();
  • Função Last_Value();
  • Função Lead();
  • Função Max();
  • Função Min();
  • Função Replace();
  • Função Stuff();
  • Geração de Totais e Subtotais;
  • Junção entre Tabelas; 
  • Movimentação de Estoques;
  • Movimentação de Saldos;
  • Níveis de Isolamento;
  • Opção For XML Path();
  • Operador Between;
  • Operador de Junção Inner Join; e
  • Saldo de Produtos.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Setembro 2019. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique à vontade para compartilhar, comentar e melhorar cada um destes códigos.


Short Scripts

— Short Script 1 – Identificando os níveis de isolamento —

Select Case transaction_isolation_level
When 0 Then ‘Unspecified’
When 1 Then ‘Read Uncommitted’
When 2 Then ‘Read Committed’
When 3 Then ‘Repeatable’
When 4 Then ‘Serializable’
When 5 Then ‘Snapshot’
End As ‘Nível de Isolamento’
FROM sys.dm_exec_sessions
Where session_id = @@SPID
Go

— Short Script 2 – Gerando totais e subtotais parciais agrupando dados sem nível de hierarquia —

Create Table Contas
(Id Int Primary key Identity(1,1),
Conta Varchar(20) Not Null,
Valor Decimal(6,2) Not Null,
Grupo Varchar(20) Not Null)
Go

 

Insert Into Contas (Conta, Valor, Grupo)
Values (‘receitasvendasUN1’ , 100, ‘receitabruta’),
(‘receitasvendasUN2’,70, ‘receitabruta’),
(‘compramateriaprima1’, 20, ‘materiaprima’),
(‘saida impostos’, 15,’Impostos’)
Go

 

— Obtendo a somatória por grupo —
;With CTESomarPorGrupo (Id, Grupo, SomatoriaGrupo)
As
(
Select Id, Grupo, Sum(Valor) Over (Partition By Grupo Order By Grupo Asc)
From Contas
Group By Id, Grupo, Valor
),
— Obtendo a somatoria parcial —
CTESomatoriaGeral (Id, Nivel1, Nivel2, Grupo, SomatoriaParcial)
As
(
Select Id,
Lead(Id,1,0) Over (Order By Id Asc) As Nivel1,
Lead(Id,2,0) Over (Order By Id Asc) As Nivel2,
Grupo,
Sum(SomatoriaGrupo) As SomatoriaGeral
From CTESomarPorGrupo
Group By Id, Grupo
)
— Obtendo as somatorias gerais —
Select Concat(CT.Grupo, ‘ – ‘,CT1.Grupo, ‘ – ‘, CT2.Grupo) As Grupos,
CT1.SomatoriaParcial As Grupo1,
CT2.SomatoriaParcial As Grupo2,
(CT1.SomatoriaParcial+CT2.SomatoriaParcial) As ‘Total Parcial’,
(Select Sum(Valor) From Contas) As ‘Somatória Geral’,
(Select Sum(Valor)+CT1.SomatoriaParcial+CT2.SomatoriaParcial From Contas) As ‘Somatória Geral Acumulado’
From CTESomatoriaGeral CT Inner Join CTESomatoriaGeral CT1
On CT1.Id = CT.Nivel1
Inner Join CTESomatoriaGeral CT2
On CT2.Id = CT.Nivel2
Order By CT.Id Desc
Go

— Short Script 3 – Analisando movimentação de Saldos e transportando para o próximo mês caso necessário —

Create Table Saldos
(Codigo Int,
Data Date,
Saldo Float)
Go

 

Insert Into Saldos
Values (1, ‘2019/08/30’, 800.00),
(1, ‘2019/09/20’, 1200.00),
(2, ‘2018/08/31’, 200.00),
(2,  ‘2019/09/28’, 800.00)
Go

 

Insert Into Saldos
Values (1, ‘2019/08/30’,  800),
(1, ‘2019/09/20’,  1200),
(2, ‘2019/08/31’,  200),
(2, ‘2019/09/28’,  800),
(3,  ‘2019/08/25’, 250)
Go

 

Select * From Saldos
Go

 

— Definindo a Maior Data Por Codigo —
;With CTEMaiorDataPorCodigo (Codigo, MaiorData)
As
(
Select Codigo, Max(Data) As MaiorData From Saldos
Group By Codigo
),
— Definindo o último Saldo Por Codigo —
CTEUltimoSaldoPorCodigo (Codigo, Data, UltimoSaldo)
As
(
Select S.Codigo, S.Data, LAST_VALUE(S.Saldo) Over (Partition By S.Codigo Order By S.Data) As UltimoSaldo
From Saldos S Inner Join CTEMaiorDataPorCodigo C
On C.Codigo = S.Codigo
And C.MaiorData = S.Data
Group By S.Codigo, S.Data, S.Saldo
),
— Definindo a Faixa de Datas —
CTEFaixasDeDatas (Codigo, DataInicial, DataFinal)
As
(
Select Codigo, Min(Data), Max(Data) From Saldos
Group By Codigo
)
— Trazendo os resultados —
Select C.Codigo,
Case
When (CT.DataInicial < CT.DataFinal) Then CT.DataFinal
Else DateAdd(Month,1,CT.DataInicial)
End As ‘Mês de Transporte’,
CU.UltimoSaldo
From CTEMaiorDataPorCodigo C Inner Join CTEUltimoSaldoPorCodigo CU
On CU.Codigo = C.Codigo
Inner Join CTEFaixasDeDatas CT
On CT.Codigo = CU.Codigo
Go

 

 

— Short Script 4 – Concatenação de Strings em conjunto com a função Stuff() —

Create Table ListaNumerica
(Numero1 Int Primary Key,
DigitoI TinyInt Default 0,
DigitoII TinyInt Default 2)
Go

 

;With CTENumeracao (Numero, DigitoI, DigitoII)
As
(Select 1, 0, 2
Union All
Select Numero + 1 As Numero, (Numero/10) As DigitoI, DigitoI+2 As DigitoII from CTENumeracao
Where Numero<100
)
Insert Into ListaNumerica
Select Numero, DigitoI, DigitoII From CTENumeracao
Go

 

Declare @Contador TinyInt = 1,
@ContadorInicial SmallInt=10,
@ContadorFinal SmallInt=19

 

While @Contador <= 12
Begin

 

Select Numero1, DigitoI,
Stuff(Replace(Replace((Select Concat(Numero1,’,’) As Numero From ListaNumerica
Where Numero1 BetWeen @ContadorInicial And @ContadorFinal For XML Path(”)),'<Numero>’,”),'</Numero>’,”),30,2,”) As ‘Lista Numérica II’,
DigitoI+1 As DigitoII
From ListaNumerica
Where Numero1 = @Contador

 

Set @Contador +=1
Set @ContadorInicial +=10
Set @ContadorFinal +=10

 

End
Go

 

— Short Script 5 – Montando Derived Tables —

Create Table TesteValores
(Valor1 Int,
Valor2 Int)
Go

 

Insert Into TesteValores Values(10,20),(5,3),(10,Null),(Null,15)
Go

 

— 1º Select denominado A —
Select Valor1, Valor2 From TesteValores As A — não declaro os paranteses
Go

 

— 2º Select denominado B, incorporando o 1º Select A —
Select Sum(Valor1) As Valor1, Sum(Valor2) As Valor2 From
(Select Valor1, Valor2 From TesteValores) As A  — acrescento o parenteses no Select A
Go

 

— 3º Select denominada C, incorporando os Selects B e A —
Select Sum(Valor1+Valor2) As Total from
(Select Sum(Valor1) As Valor1, Sum(Valor2) As Valor2 From  — acrescento o parenteses no Select B
(Select Valor1, Valor2 From TesteValores) As A) As B — definido o apelido do Select B
Go

 

— Select Derived Table —

Select Sum(Valor1+Valor2) As Total

From
(Select Sum(Valor1) As Valor1, Sum(Valor2) As Valor2

From
(Select Valor1, Valor2 From TesteValores) As A) As B
Go

 


Muito bem, missão mais que cumprida!

Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados shorts entre aspas “curtos ou pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2019/06/08/short-scripts-junho-2019/

https://pedrogalvaojunior.wordpress.com/2019/03/15/short-scripts-marco-2019/

https://pedrogalvaojunior.wordpress.com/2018/11/20/short-scripts-novembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/09/18/short-scripts-setembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/05/10/short-scripts-maio-2018/

 

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post a ser publicado no mês de novembro.

Um forte abraço, saúde e paz.

Até mais.