Trabalhando com datas – Scripts para trabalhar com os dias de um mês

E ai pessoas,

Nestes dias, dando uma olhada em alguns rascunhos do blog, encontrei quatro scripts que considero interessante compartilhar, principalmente pelo fato deles evoluírem de um para outro de forma bem clara.

O primeiro script recupera o último dia do mês:

DECLARE @Mes INT = 2, @Ano INT = 2011

DECLARE @DtaFinal DATETIME

SET @DtaFinal = DATEADD(YEAR, @Ano - 1900, DATEADD(MONTH, @Mes, 0)) - 1

SELECT CONVERT(VARCHAR, @DtaFinal, 103) AS [Último dia de um determinado mês]

GO

Este segundo, usa a função DATEPART para recuperar a quantidade de dias do mês:

DECLARE @Mes INT = 2, @Ano INT = 2011

DECLARE @DtaFinal DATETIME

SET @DtaFinal = DATEADD(YEAR, @Ano - 1900, DATEADD(MONTH, @Mes, 0)) - 1

SELECT DATEPART(DAY, @DtaFinal) AS [Número de dias de um determinado mês]

GO

Este terceiro script, usa uma CTE para relacionar os dias de um determinado mês:

DECLARE @Mes INT = 2, @Ano INT = 2011

DECLARE @Num INT
DECLARE @DtaInicio DATETIME
DECLARE @DtaFinal DATETIME

SET @DtaInicio = DATEADD(YEAR, @Ano - 1900, DATEADD(MONTH, @Mes - 1, 0))
SET @DtaFinal = DATEADD(YEAR, @Ano - 1900, DATEADD(MONTH, @Mes, 0)) - 1

;WITH CTE (Dta) AS (
	SELECT @DtaInicio
	UNION ALL
	SELECT Dta + 1
	FROM CTE
	WHERE Dta < @DtaFinal
)

SELECT Dta AS [Dia] FROM CTE

GO

E este ultimo script, usa um CASE em combinação de um COUNT para contar a quantidade de sábados e domingos de um determinado mês:

DECLARE @Mes INT = 2, @Ano INT = 2011

DECLARE @Num INT
DECLARE @DtaInicio DATETIME
DECLARE @DtaFinal DATETIME

SET @DtaInicio = DATEADD(YEAR, @Ano - 1900, DATEADD(MONTH, @Mes - 1, 0))
SET @DtaFinal = DATEADD(YEAR, @Ano - 1900, DATEADD(MONTH, @Mes, 0)) - 1

;WITH CTE (Dta) AS (
	SELECT @DtaInicio
	UNION ALL
	SELECT Dta + 1
	FROM CTE
	WHERE Dta < @DtaFinal
)

--DATEPART(W, Dta) = 1 // Domingo
--DATEPART(W, Dta) = 7 // Sábado
SELECT @Num = COUNT(CASE WHEN DATEPART(W, Dta) IN (1, 7) THEN 1 END) FROM CTE

SELECT @Num AS [Número de sábado e domingos de um determinado mês]

GO

Artigos relacionados:

Descobrindo o primeiro dia e o número de semanas de um mês

Lista de meses e dias da semana

Recuperando informações de dia, mes, ano, hora, minuto, segundo de datas

Trabalhando com formatação de datas

Criando um calendário pelo SQL

FUNÇÕES: Somente Números (com CTE) Versão 2

[2011-04-02] Código completo – Somente números:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo – Somente texto:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo – Remover caracteres especiais:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO
[Post-Original] Explicação:

Boa noite pessoas!

Já faz algum tempo que escrevi as funções “somente números” com WHILE e CTE, até que estes dias eu tive de reformular elas de forma a remover caracteres especiais, remover máscaras e afins, assim como para outras funcionalidades mais absurdas… O legal foi que causou uma leve mudança da estrutura original da minha CTE antiga, resultando numa terceira função “somente números” bem interessante, que facilmente pode ser tranformada em “somente letras”, “somente números e letras”, “somente caractéres internacionais”… Ok, famos ao que interessa!

Primeiramente temos o texto:

DECLARE @Texto NVARCHAR(MAX)
SET @Texto = N'O azul é uma das três cores-luz primárias,
e cor-pigmento secundária, resultado da sobreposição dos
pigmentos ciano e magenta. Seu comprimento de onda é da
ordem de 455 a 492 nanômetros do espectro de cores visíveis.';

Em seguida uma variável que receberá o resultado:

DECLARE @Result NVARCHAR(MAX)
SET @Result = ''

E a expressão/CTE de split de caractere por caractere:

;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@Texto, 1, 1) AS CH
UNION ALL
SELECT ID + 1, SUBSTRING(@Texto, ID + 1, 1)
FROM SPLIT
WHERE ID < LEN(@Texto)
)

E agora para filtrar e juntar todos os caracteres:

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[0-9]'
OPTION (MAXRECURSION 0)

Enfim exibindo o resultado:

SELECT @Result AS [Resultado]

Também é possível adaptar a consulta para “somente letras” ou “somente alfa-numéricos” (removendo os caracteres especiais):

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z]'
OPTION (MAXRECURSION 0)

-- OU

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z0-9]'
OPTION (MAXRECURSION 0)

Mas estas consultas também podem ter mais utilidade com um CASE substituindo os caracteres especiais por espaço ou outro caractere qualquer:

SELECT @Result = @Result
+ (CASE WHEN CH LIKE '[A-z0-9]' THEN CH ELSE '' END)
FROM SPLIT
OPTION (MAXRECURSION 0)

Mas sempre é bom lembrar que é melhor ter rotinas deste gênero na aplicação no lugar do banco de dados ou pensar em CLR.

Obrigado Eduardo Ruthes pela idéia inicial desta CTE!!!

Artigos relacionados:

FUNÇÕES: Validação de CNPJ e CPF
https://sqlfromhell.wordpress.com/2009/11/19/validacao-de-cnpj-e-cpf/

FUNÇÕES: Somente Números (com CTE) [Original]
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/

FUNÇÕES: Somente Números (com WHILE)
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

FUNÇÕES: Somente Números (com CTE)

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE
	@RESULTADO VARCHAR (8000),
	@ZERO INT,
	@NOVE INT

SELECT
   @ZERO = UNICODE(0),
   @NOVE = UNICODE(9)

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)
, NUMEROS AS
(
	SELECT LETRA
	FROM SPLIT
	WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo v2:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)

SELECT @RESULTADO
[Post-Original] Explicação:

Boa noite pessoal!

Continuando o artigo anterior, vou demonstrar como recuperar somente os valores numéricos de um texto, agora com CTE.

Para começar, tenho o mesmo cenário do artigo anterior, uma variável com um texto absurdo, da qual desejo somente retornar os valores numéricos.

DECLARE @PALAVRA VARCHAR(8000)
SET @PALAVRA = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'
</code></div>
Como estou utilizando CTE, não tenho necessidade de muitas variáveis.
<div style="border: 1px; background-color: #000000; color: #ffffff; font-weight: bold; border-color: #ffffff; margin: 5px; padding: 5px;"><code>DECLARE
@RESULTADO VARCHAR (8000),
@ZERO INT,
@NOVE INT

Mas ainda continuo a ter a necessidade das variáveis que delimitam o RANGE dos valores numéricos, no caso os ‘valores decimais’ dos caracteres 0 (zero) e 9 (nove):

SELECT
@ZERO = UNICODE(0),
@NOVE = UNICODE(9)

Com a primeira CTE, realizo crio a relação de um a um dos caracteres do meu texto (um caractere por linha). Na segunda CTE, aplico a validação do RANGE de cada um dos caracteres relacionados na primeira CTE.

;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@PALAVRA, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@PALAVRA, ID + 1, 1)
FROM SPLIT
WHERE ID &lt; LEN(@PALAVRA)
)
, NUMEROS AS
(
SELECT LETRA
FROM SPLIT
WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

Por fim, utilizo uma query para concatenar os valores resultantes da segunda CTE, adicionando o HINT MAXRECURSION, para evitar que um texto com mais de 100 caracteres prejudique minha operação.

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 8000)

No próximo artigo trarei a validação de CPF e CNPJ em uma única operação (e de forma otimizada).

Artigos relacionados:

CTE – Introdução:
https://sqlfromhell.wordpress.com/2009/09/20/cte-introducao/

FUNÇÕES: Somente Números (com WHILE):
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

FUNÇÕES: Listas de valores numéricos

Boa noite pessoal!

Estive pensando sobre algumas formas de ajudar vocês em necessidades do dia-dia, assim tive a idéia de nas próximas semanas trazer uma relação de queries que facilitam estas necessidades. Uma que eu sempre me deparo é gerar listas numéricas, assim elaborei este artigo para ajuda-los a nesta situação.

Então para uma lista de valores numéricos e tenho as seguintes alternativas:

1. Criar tabela temporária e inserir os registros um a um dentro de um WHILE? Inserir os registros um a um não será tão perfomático, mas se o fizer, não posso esquecer do NOCOUNT, para evitar as mensagens de ‘linhas afetadas’:

SET NOCOUNT ON;

2. Fazer uma consulta com vários UNIONs? Mas quantas linhas mesmo eu vou ter que digitar? Algo em torno de duas linhas para cada valor numérico? Para listas grandes… Nem pensar…

--Criando uma lista de 0-9 sem recursividade.
SELECT 0 AS ID
UNION ALL
  SELECT 1
UNION ALL
  SELECT 2
UNION ALL
  SELECT 3
UNION ALL
  SELECT 4
UNION ALL
  SELECT 5
UNION ALL
  SELECT 6
UNION ALL
  SELECT 7
UNION ALL
  SELECT 8
UNION ALL
  SELECT 9

3. Fazer uma CTE recursiva? Pode ser, a única desvantagem será algo próximo a 9,13 leituras por linha durante a execução do código e o limite de recursividade.

--Criando uma lista de 0-9 com recursividade.
;WITH Lista AS
(
  SELECT 0 AS ID
UNION ALL
  SELECT ID + 1
  FROM Lista
  WHERE ID < 9
)
SELECT * FROM Lista

4. Então vamos tornar o processo prático e deixar o código mais “limpo”, criarei uma função que retornara uma lista gerada com CTE, passando como argumentos o range a ser utilizado.

--Função
CREATE FUNCTION Lista(@inicial INT, @final INT)
RETURNS @Resultado TABLE (valor INT)
AS
BEGIN 
  IF @inicial <= @final
  BEGIN
    WITH CTE AS
    (
      SELECT @inicial AS valor
      UNION ALL
        SELECT valor + 1
        FROM CTE
        WHERE valor < @final
    )
    INSERT INTO @Resultado (valor)
      SELECT valor
      FROM CTE
      OPTION (MAXRECURSION 0)
  END
  ELSE 
  BEGIN
    WITH CTE AS
    (
      SELECT @inicial AS valor
      UNION ALL
        SELECT valor - 1
        FROM CTE
        WHERE valor > @final
    )
    INSERT INTO @Resultado (valor)
      SELECT valor
      FROM CTE
      OPTION (MAXRECURSION 0)
  END   
  RETURN;
END;

E as chamadas para os ranges variados.

SELECT valor FROM dbo.Lista(-10, 100000);
SELECT valor FROM dbo.Lista(10, -100000);

5. Mas no final a melhor alternativa é criar uma tabela no banco de dados com os valores que serão utilizados.

Se alguém tiver alguma outra alternativa, estou disposto a testar. Para o próximo artigo, estou pensando em funções para validação de valores numéricos.

Artigos relacionados:

CTE – Introdução:
https://sqlfromhell.wordpress.com/2009/09/20/cte-introducao/

WHILE vs CTE – Popular tabela de testes:
https://sqlfromhell.wordpress.com/2009/09/12/while-vs-cte-popular-tabela-de-testes/

Trabalhando com Datas – Lista de meses e dias da semana:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

SQL Server Day – Grande tecnologia, por grandes profissionais!

sqlserverday

O evento SQL Server Day 2009 trará mais de 12 horas de palestras com os maiores nomes de SQL Server no Brasil. Este primeiro evento será realizado no dia 07/11/2009 começando às 9 hs da manhã, mas como é on-line, o coffee break fica por sua conta!

Pelo post do Zavaschi, a grade do evento será a seguinte:

09:30 – 10:00 – Abertura do Evento
10:00 – 11:00 – Resource Monitor e Policy Management – Vitor Fava e Alexandre Lopes
11:00 – 12:00 – Entenda porque o Query Optimizer é mais esperto que você – Fabiano Amorim
12:00 – 13:00 – SQL Azure Database – Diego Nogare
13:00 – 14:00 – Novos Recursos de Desenvolvimento do SQL Server 2008 – Higor Fernandes
14:00 – 15:00 – Compressão de Dados e Backup no Microsoft SQL Server 2008 – Pedro A. G. Junior
15:00 – 16:00 – Entendendo TDE (Transparent Data Encryption) – Felipe Ferreira
16:00 – 17:00 – Entendendo as Common Table Expressions (CTE) – Thiago Zavaschi
17:00 – 18:00 – Powershell Coletando e Analisando os Dados – Laerte Junior e Thiago Zavaschi
18:00 – 19:00 – Solução Avançada de Problemas com Extended Events – Vladimir Magalhães
19:00 – 20:00 – Disaster Recovery – Backup, Restore e Tópicos Avançados – Gustavo Maia Aguiar
20:00 – 21:00 – Integre seu código .NET com o SQL Server usando o CLR – Roberto Fonseca
21:00 – 22:00 – Analise de Desempenho utilizando as Estatísticas de Espera – Alex Rosa

 

Mais informações:

SQL Server Day – Site Oficial
http://www.sqlserverday.com.br

CTE – Introdução

Para quem já têm acompanhado meus posts, deve ter percebido minha “afeição” às CTEs (Common Table Expression) do SQL Server 2005/2008.

Mas para salvação daqueles que não conhecem este recurso, o Thiago Zavaschi (um ótimo DBA e amigo com o qual tenho a honra de trabalhar na TechResult e no MIC Curitiba-PR) tomou a iniciativa de escrever dois post sobre o assunto (na verdade um post divido em duas partes).

Abaixo o link para os posts:

Entendendo as Common Table Expressions – CTE:
http://tinyurl.com/nbflaf (parte 1)
http://tinyurl.com/lrvmyp (parte 2)

Site do Thiago Zavaschi:
http://www.zavaschi.com/  

Para quem quiser saber ainda mais sobre o assunto, o Gustavo Maia Aguiar escreveu um artigo ótimo sobre o assunto na revista Mundo.Net deste mês:
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!695.entry 

 

Posts relacionados:

WHILE vs CTE – Popular tabela de testes:
https://sqlfromhell.wordpress.com/2009/09/12/while-vs-cte-popular-tabela-de-testes/

Trabalhando com Datas – Lista de meses e dias da semana:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

WHILE vs CTE – Popular tabela de testes

Boa noite pessoas!

Por questões pessoais acabei não sendo muito fã de WHILE (laço de repetição) em código T-SQL, principalmente pelo fato de WHILE lembrar CURSOR e CURSOR levam a algumas rotinas nada performáticas, no lugar de desenvolver consultas SQL um pouco mais elaboradas.

Para popular tabelas de testes e demonstração é comum encontrar exemplo com WHILE, mas quero deixar minha proposta utilizando CTE.

No código abaixo crio uma tabela comum encontrada em demonstrações de Sql Tuning, por utilizar uma coluna do tipo char de tamanho quase absurdo.

Obs.: Se for utilizar WHILE, utilize “SET NOCOUNT ON” para evitar ter receber um monte de mensagens de “linhas afetadas”.

CREATE TABLE TabelaDeTestes
(
  idTab INT,
  textoTab CHAR(5000)
)
GO

Declaro a variável que será incrementada durante a execução do WHILE:

DECLARE @I INT
SET @I = 0

Enfim o desenvolvo o WHILE populando a tabela de teste com a variável incrementada e um texto gerado pela função NEWID (Função responsável por gerar GUID no T-SQL).

WHILE @I < 1000
BEGIN
  SET @I = @I + 1
  INSERT INTO TabelaDeTestes (idTab, textoTab)
  VALUES (@I, NEWID())
END
GO

 Ok, feito o método tradicional com WHILE, vamos para a minha proposta com CTE. Primeiramente crio um CTE recursivo. 

;WITH Cte (idTab, textoTab)
AS
(
SELECT 1, NEWID()
UNION ALL
SELECT idTab + 1, NEWID() FROM Cte
WHERE idTab <= 1000
)

Depois um tradicional “INSERT INTO tabela SELECT” com um HINT para aumentar o limite de recursividade da CTE para 1000. 

INSERT INTO TabelaDeTestes (idTab, textoTab)
SELECT idTab, textoTab FROM Cte
OPTION (MAXRECURSION 1000)

De qualquer forma CTE possui várias limitações, seja pelo número de recursividades que por padrão é 100 e com o HINT utilizado até 32767, mas seu desempenho é muito superior as ronitas desenvolvidas com o WHILE, vale a pena testar.

Espero que tenham gostado do post e os motivado a desenvolver consultas bem elaboradas em SQL, deixando sempre como última alternativa utilização de estruturas WHILE e afins, então pessoas até o próximo post!

 

Posts relacionados:

Gerando lista de meses e dias da semana com CTE:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

Gerando consultas desordenadas e aleatórias:
https://sqlfromhell.wordpress.com/2009/07/25/consultas-desordenadas-e-aleatorias/