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: 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/

(DRAFT) Realizando “UNPIVOT” de texto concatenado:

Criando a tabela para a demonstração:

CREATE TABLE TAB (CentroDeCusto VARCHAR(100), Pedido VARCHAR(4))
GO
INSERT INTO TAB
VALUES ('0012/0258/0255','0222')
GO
INSERT INTO TAB
VALUES ('0032/6369/5255/8541','0223')
GO


Para SQL Server 2005 e 2008, criando uma função de split:

CREATE FUNCTION SPLIT (@Texto VARCHAR(100), @Delimitador CHAR)
RETURNS @Resultado TABLE
(
Final VARCHAR(100) NOT NULL
)
AS
BEGIN
;WITH _SPLIT(ID, _INDEX, _LENGTH) AS
(
SELECT
1,
1,
CHARINDEX(@Delimitador, @Texto + @Delimitador)
UNION ALL
SELECT
ID + 1,
_LENGTH + 1,
CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1)
FROM _SPLIT
WHERE CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1) <> 0
)
, _SPLIT2 (Id, Valores) AS
(
SELECT
ID,
SUBSTRING(@Texto, _INDEX, _LENGTH - _INDEX)
FROM _SPLIT
)
INSERT INTO @Resultado
SELECT Valores FROM _SPLIT2
RETURN
END;
GO


Para SQL Server 2005 e 2008, executando utilizando CROSS APPLY:

SELECT TAB2.Final AS CentroDeCusto, TAB.Pedido
FROM TAB
CROSS APPLY (SELECT Final FROM SPLIT(CentroDeCusto, '/')) AS TAB2


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/