CREATE FUNCTION [dbo].[FN_DIAUTIL](@DataMes DateTime)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @FORMATO VARCHAR(3)
SELECT @FORMATO = DATEFORMAT FROM sys.syslanguages S WHERE S.NAME = @@LANGUAGE
DECLARE @PASCOA DATETIME
SELECT @PASCOA = CASE DATEPART(WeekDay,CAST(DD.DIADOURADO AS DATETIME))
WHEN 1 THEN DATEPART(WeekDay,CAST(DD.DIADOURADO AS DATETIME))
ELSE DATEADD(day, (8-DATEPART(WeekDay,CAST(DD.DIADOURADO AS DATETIME))) ,DD.DIADOURADO)
END
FROM (SELECT
CASE
WHEN (YEAR(@DataMes) % 19)+1 = 1 AND @FORMATO = 'mdy' THEN '04.14.'
WHEN (YEAR(@DataMes) % 19)+1 = 2 AND @FORMATO = 'mdy' THEN '04.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 3 AND @FORMATO = 'mdy' THEN '03.23.'
WHEN (YEAR(@DataMes) % 19)+1 = 4 AND @FORMATO = 'mdy' THEN '04.11.'
WHEN (YEAR(@DataMes) % 19)+1 = 5 AND @FORMATO = 'mdy' THEN '03.31.'
WHEN (YEAR(@DataMes) % 19)+1 = 6 AND @FORMATO = 'mdy' THEN '04.18.'
WHEN (YEAR(@DataMes) % 19)+1 = 7 AND @FORMATO = 'mdy' THEN '04.08.'
WHEN (YEAR(@DataMes) % 19)+1 = 8 AND @FORMATO = 'mdy' THEN '03.28.'
WHEN (YEAR(@DataMes) % 19)+1 = 9 AND @FORMATO = 'mdy' THEN '04.16.'
WHEN (YEAR(@DataMes) % 19)+1 =10 AND @FORMATO = 'mdy' THEN '04.05.'
WHEN (YEAR(@DataMes) % 19)+1 =11 AND @FORMATO = 'mdy' THEN '03.25.'
WHEN (YEAR(@DataMes) % 19)+1 =12 AND @FORMATO = 'mdy' THEN '04.13.'
WHEN (YEAR(@DataMes) % 19)+1 =13 AND @FORMATO = 'mdy' THEN '04.02.'
WHEN (YEAR(@DataMes) % 19)+1 =14 AND @FORMATO = 'mdy' THEN '03.22.'
WHEN (YEAR(@DataMes) % 19)+1 =15 AND @FORMATO = 'mdy' THEN '04.10.'
WHEN (YEAR(@DataMes) % 19)+1 =16 AND @FORMATO = 'mdy' THEN '03.30.'
WHEN (YEAR(@DataMes) % 19)+1 =17 AND @FORMATO = 'mdy' THEN '04.17.'
WHEN (YEAR(@DataMes) % 19)+1 =18 AND @FORMATO = 'mdy' THEN '04.07.'
WHEN (YEAR(@DataMes) % 19)+1 =19 AND @FORMATO = 'mdy' THEN '03.27.'
WHEN (YEAR(@DataMes) % 19)+1 = 1 AND @FORMATO = 'dmy' THEN '14.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 2 AND @FORMATO = 'dmy' THEN '03.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 3 AND @FORMATO = 'dmy' THEN '23.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 4 AND @FORMATO = 'dmy' THEN '11.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 5 AND @FORMATO = 'dmy' THEN '31.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 6 AND @FORMATO = 'dmy' THEN '18.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 7 AND @FORMATO = 'dmy' THEN '08.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 8 AND @FORMATO = 'dmy' THEN '28.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 9 AND @FORMATO = 'dmy' THEN '16.04.'
WHEN (YEAR(@DataMes) % 19)+1 =10 AND @FORMATO = 'dmy' THEN '05.04.'
WHEN (YEAR(@DataMes) % 19)+1 =11 AND @FORMATO = 'dmy' THEN '25.03.'
WHEN (YEAR(@DataMes) % 19)+1 =12 AND @FORMATO = 'dmy' THEN '13.04.'
WHEN (YEAR(@DataMes) % 19)+1 =13 AND @FORMATO = 'dmy' THEN '02.04.'
WHEN (YEAR(@DataMes) % 19)+1 =14 AND @FORMATO = 'dmy' THEN '22.03.'
WHEN (YEAR(@DataMes) % 19)+1 =15 AND @FORMATO = 'dmy' THEN '10.04.'
WHEN (YEAR(@DataMes) % 19)+1 =16 AND @FORMATO = 'dmy' THEN '30.03.'
WHEN (YEAR(@DataMes) % 19)+1 =17 AND @FORMATO = 'dmy' THEN '17.04.'
WHEN (YEAR(@DataMes) % 19)+1 =18 AND @FORMATO = 'dmy' THEN '07.04.'
WHEN (YEAR(@DataMes) % 19)+1 =19 AND @FORMATO = 'dmy' THEN '27.03.'
END + CAST(YEAR(@DataMes) AS VARCHAR(4)) AS DIADOURADO
) AS DD
DECLARE @TB_FERIADOS TABLE (DataMes DateTime, Descricao VARCHAR(100))
IF @FORMATO = 'dmy'
BEGIN
INSERT INTO @TB_FERIADOS SELECT '01-01-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Confraternização Universal'
INSERT INTO @TB_FERIADOS SELECT '21-04-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Tiradentes'
INSERT INTO @TB_FERIADOS SELECT '01-05-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Dia do Trabalho'
INSERT INTO @TB_FERIADOS SELECT '07-09-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Independencia do Brasil'
INSERT INTO @TB_FERIADOS SELECT '12-10-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Nossa Senhora Aparecida'
INSERT INTO @TB_FERIADOS SELECT '02-11-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Finados'
INSERT INTO @TB_FERIADOS SELECT '15-11-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Proclamação da República'
INSERT INTO @TB_FERIADOS SELECT '25-12-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Natal'
END
IF @FORMATO = 'mdy'
BEGIN
INSERT INTO @TB_FERIADOS SELECT '01-01-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Confraternização Universal'
INSERT INTO @TB_FERIADOS SELECT '04-21-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Tiradentes'
INSERT INTO @TB_FERIADOS SELECT '05-01-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Dia do Trabalho'
INSERT INTO @TB_FERIADOS SELECT '09-07-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Independencia do Brasil'
INSERT INTO @TB_FERIADOS SELECT '10-12-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Nossa Senhora Aparecida'
INSERT INTO @TB_FERIADOS SELECT '11-02-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Finados'
INSERT INTO @TB_FERIADOS SELECT '11-15-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Proclamação da República'
INSERT INTO @TB_FERIADOS SELECT '12-25-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Natal'
END
INSERT INTO @TB_FERIADOS SELECT @Pascoa-47 ,'Carnaval'
INSERT INTO @TB_FERIADOS SELECT @Pascoa-2 ,'Sexta Feira Santa'
INSERT INTO @TB_FERIADOS SELECT @Pascoa+60 ,'Corpus Christi'
DECLARE @NOMEDIA VARCHAR(13)
SET @NOMEDIA = CASE DatePart(WeekDay, @DataMes)
WHEN 1 THEN 'Domingo'
WHEN 2 THEN 'Segunda-Feira'
WHEN 3 THEN 'Terça-Feira'
WHEN 4 THEN 'Quarta-Feira'
WHEN 5 THEN 'Quinta-Feira'
WHEN 6 THEN 'Sexta-Feira'
WHEN 7 THEN 'Sábado'
END
DECLARE @RETORNO VARCHAR(100)
IF (DatePart(WeekDay, @DataMes) BETWEEN 2 AND 6) AND @DataMes NOT IN (SELECT DataMes FROM @TB_FERIADOS)
SET @RETORNO = 'S'
ELSE
SET @RETORNO = 'N'
SET @RETORNO = @RETORNO + ' - ' + @NOMEDIA
IF @DataMes IN (SELECT DataMes FROM @TB_FERIADOS)
SET @RETORNO = @RETORNO + ' - ' +(SELECT Descricao FROM @TB_FERIADOS WHERE DataMes = @DataMes)
RETURN @RETORNO
END
SELECT DBO.FN_DIAUTIL('11.02.2015') AS DIA_UTIL
Mostrando postagens com marcador T-SQL. Mostrar todas as postagens
Mostrando postagens com marcador T-SQL. Mostrar todas as postagens
sábado, 16 de janeiro de 2016
Funcao Dia Util SQL Server
Função que recebe uma data como parâmetro e apresenta se o dia é útil, considerando feriados nacionais brasileiros.
Funcao Valida CPF ou CNPJ
CREATE FUNCTION [dbo].[Valida_CPFCNPJ](@TEXTO varchar(20)) RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @RESULTADO VARCHAR(20)
DECLARE @CPF_CNPJ VARCHAR(20)
WHILE PATINDEX('%[^0-9]%', @TEXTO) > 0
BEGIN
SET @TEXTO = STUFF(@TEXTO, PATINDEX('%[^0-9]%', @TEXTO), 1, '')
END
SET @CPF_CNPJ = @TEXTO
IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
SET @RESULTADO = 'Inválido'
RETURN @RESULTADO
END
DECLARE @DIGITO1 INT, @DIGITO2 INT, @VALOR1 INT, @VALOR2 INT
DECLARE @I INT, @J INT, @TOTAL_TMP INT, @COEFICIENTE_TMP INT
, @DIGITO_TMP INT, @VALOR_TMP INT
SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)
SET @J = 1
WHILE @J <= 2
BEGIN
SELECT @TOTAL_TMP = 0, @COEFICIENTE_TMP = 2
SET @I = ((LEN(@CPF_CNPJ) - 3) + @J)
WHILE @I >= 0
BEGIN
SELECT
@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1
IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
SET @COEFICIENTE_TMP = 2
SET @I = @I - 1
END
SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
IF (@VALOR_TMP >= 10)
SET @VALOR_TMP = 0
IF @J = 1
SET @VALOR1 = @VALOR_TMP
ELSE
SET @VALOR2 = @VALOR_TMP
SET @J = @J + 1
END
SELECT @RESULTADO =
CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
THEN 'Válido'
ELSE 'Inválido'
END
RETURN @RESULTADO
END
sábado, 27 de julho de 2013
Brincando com os operadores de comparação ( ANY | SOME e ALL )
Vou apresentar desta vez operadores de comparação que muitos desconhecem, são eles: ( ANY |SOME e ALL )
DECLARE @TABELA TABLE(ID INT)
INSERT INTO @TABELA SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
--ANY retorna verdadeiro comparando por QUALQUER valor de um Conjunto
IF 2 < ANY(SELECT ID FROM @TABELA)
BEGIN
PRINT '2 É MENOR QUE QUALQUER VALOR DA TABELA'
END
--SOME retorna verdadeiro comparando por ALGUM valor de um Conjunto
IF 2 < SOME(SELECT ID FROM @TABELA)
BEGIN
PRINT '2 É MENOR QUE ALGUM VALOR DA TABELA'
END
--ALL retorna verdadeiro comparando por TODOS valores de um Conjunto
IF 0 < ALL(SELECT ID FROM @TABELA)
BEGIN
PRINT '0 É MENOR QUE TODOS OS VALORES DA TABELA'
END
--CURIOSIDADES: EQUIVALENCIA ANY E IN
--Trazer dados de @TABELA que seja IGUAL a QUALQUER valor do Conjunto
SELECT *
FROM @TABELA
WHERE ID = ANY(SELECT 1 UNION SELECT 2 UNION SELECT 5)
--Trazer dados de @TABELA que seja valores CONTIDOS num Conjunto
SELECT *
FROM @TABELA
WHERE ID IN(SELECT 1 UNION SELECT 2 UNION SELECT 5)
/*
Resultado em ambos os casos
ID
-----------
1
2
*/
--CURIOSIDADES: EQUIVALENCIA ALL e NOT IN
--Trazer dados de @TABELA que seja diferente de TODOS valores do Conjunto
SELECT *
FROM @TABELA
WHERE ID <> ALL(SELECT 1 UNION SELECT 2 UNION SELECT 5)
--Trazer dados de @TABELA que seja valores NAO CONTIDOS em um Conjunto
SELECT *
FROM @TABELA
WHERE ID NOT IN (SELECT 1 UNION SELECT 2 UNION SELECT 5)
/*
Resultado em ambos os casos
ID
-----------
3
4
*/
quinta-feira, 4 de julho de 2013
SQL Server - Order By Dinâmico
Vamos a outra brincadeira com SQL-Server
Em alguns sistemas existe a característica de ordenação conforme click na coluna Header, então segue um exemplo que diz respeito a este tratamento usando Banco de Dados SQL-Server
Com o trecho abaixo pode ser informado qual o número da coluna que se deseja ordenar:
Já o próximo trecho de código resulta no mesmo resultado, mas utilizando-se do nome da coluna que se deseja ordenar:
Com o trecho abaixo pode ser informado qual o número da coluna que se deseja ordenar:
DECLARE @TABELA TABLE(ID INTEGER
, DESCRICAO VARCHAR(100)
, CADASTRO DATETIME
, IDADE INTEGER
, SITUACAO CHAR(1))
DECLARE @ORDERBY INTEGER
--LEGENDA DE ORDENACAO:
--[1 - ID]
--[2 - DESCRICAO]
--[3 - CADASTRO]
--[4 - IDADE]
--[5 - SITUACAO]
SET @ORDERBY = 1
INSERT INTO @TABELA VALUES(1, 'Z', '01-01-2013 00:00:00', 10, 'A')
INSERT INTO @TABELA VALUES(2, 'X', '03-03-2013 00:00:00', 40, 'I')
INSERT INTO @TABELA VALUES(3, 'Y', '02-02-2013 00:00:00', 30, 'A')
SELECT ID, DESCRICAO, CADASTRO, IDADE, SITUACAO
FROM @TABELA
ORDER BY CASE --ORDENAÇÃO NUMÉRICA
WHEN @ORDERBY = 1 THEN ID
WHEN @ORDERBY = 3 THEN CADASTRO
WHEN @ORDERBY = 4 THEN IDADE
END
,CASE --ORDENAÇÃO NÃO NUMÉRICA
WHEN @ORDERBY = 2 THEN DESCRICAO
WHEN @ORDERBY = 5 THEN SITUACAO
END
Já o próximo trecho de código resulta no mesmo resultado, mas utilizando-se do nome da coluna que se deseja ordenar:
DECLARE @TABELA TABLE(ID INTEGER
, DESCRICAO VARCHAR(100)
, CADASTRO DATETIME
, IDADE INTEGER
, SITUACAO CHAR(1))
DECLARE @ORDERBY VARCHAR(20)
SET @ORDERBY = 'SITUACAO'
INSERT INTO @TABELA VALUES(1, 'Z', '01-01-2013 00:00:00', 10, 'A')
INSERT INTO @TABELA VALUES(2, 'X', '03-03-2013 00:00:00', 40, 'I')
INSERT INTO @TABELA VALUES(3, 'Y', '02-02-2013 00:00:00', 30, 'A')
SELECT ID, DESCRICAO, CADASTRO, IDADE, SITUACAO
FROM @TABELA
ORDER BY CASE --ORDENAÇÃO NUMÉRICA
WHEN @ORDERBY = 'ID' THEN ID
WHEN @ORDERBY = 'CADASTRO ' THEN CADASTRO
WHEN @ORDERBY = 'IDADE' THEN IDADE
END
,CASE --ORDENAÇÃO NÃO NUMÉRICA
WHEN @ORDERBY = 'DESCRICAO' THEN DESCRICAO
WHEN @ORDERBY = 'SITUACAO' THEN SITUACAO
END
domingo, 26 de maio de 2013
Cursor em SQL-Server (Números Primos)
Lembrando que um cursor deve ser usado somente quando não é possível resolver o que se necessita com apenas SELECT,
Para o nosso exemplo de Cursor, utilizo números que estão em uma "Tabela" e quero saber quais deles são primos.
Resultado:
DECLARE @ID INT
DECLARE @DESCRICAO VARCHAR(100)
DECLARE @DIVISOR INT
DECLARE @PRIMO BIT
--Declaração de um Cursor.
DECLARE CUR CURSOR FOR SELECT 1 ID,'Um' DESCRICAO UNION ALL
SELECT 2, 'Dois' UNION ALL
SELECT 3, 'Tres' UNION ALL
SELECT 4, 'Quatro' UNION ALL
SELECT 5, 'Cinco' UNION ALL
SELECT 6, 'Seis' UNION ALL
SELECT 7, 'Sete'
--Abrindo um Cursor para uso.
OPEN CUR
--Leitura da tupla do cursor dentro das variáveis
FETCH NEXT FROM CUR INTO @ID, @DESCRICAO
--Caso exista dados no Select do Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PRIMO = 1 --Número é primo
SET @DIVISOR = @ID-1
WHILE @DIVISOR > 1
BEGIN
IF @ID % @DIVISOR = 0
BEGIN
SET @PRIMO = 0 --Número não é primo
BREAK
END
SET @DIVISOR = @DIVISOR -1
END
IF @PRIMO = 0 PRINT 'O número '+@DESCRICAO+' não é primo!'
ELSE PRINT 'O número '+@DESCRICAO+' é primo!'
--Leitura da tupla do cursor dentro das variáveis
FETCH NEXT FROM CUR INTO @ID, @DESCRICAO
END
--Fechando um cursor.
CLOSE CUR
--Liberando o cursor da memória (obrigatório)
DEALLOCATE CUR
Resultado:
O número Um é primo!
O número Dois é primo!
O número Tres é primo!
O número Quatro não é primo!
O número Cinco é primo!
O número Seis não é primo!
O número Sete é primo!
sábado, 25 de maio de 2013
Preenchendo texto a direita com caracter
Neste exemplo vamos preencher um texto com algum caracter (*) a direita:
Resultado:
DECLARE @NUMBER VARCHAR(20)
DECLARE @COUNT INTEGER
SET @NUMBER = 'Texto'
SET @COUNT = 10
SELECT '1234567890' AS FORMATED UNION ALL
SELECT @NUMBER + REPLICATE('*',@COUNT - LEN(@NUMBER))
Resultado:
FORMATED
-----------
1234567890
Texto*****
Apenas Números em SQL-Server
Acontece de encontrarmos situações para:
>> Corrigir dados já persistidos por um sistema
>> Precisarmos validar um arquivo de carga para efetivarmos no banco de dados
Uma dessas validações é exatamente retirar dados não numéricos de um texto, segue um código para deixar apenas números a partir de um texto:
Resultado:
>> Corrigir dados já persistidos por um sistema
>> Precisarmos validar um arquivo de carga para efetivarmos no banco de dados
Uma dessas validações é exatamente retirar dados não numéricos de um texto, segue um código para deixar apenas números a partir de um texto:
DECLARE @TEXTO VARCHAR(MAX)
SET @TEXTO = 'abc123dc4fda$%#'
DECLARE @INDEX SMALLINT
SET @INDEX = PATINDEX('%[^0-9]%', @TEXTO)
WHILE @INDEX > 0
BEGIN
SET @TEXTO = STUFF(@TEXTO, @INDEX, 1, '')
SET @INDEX = PATINDEX('%[^0-9]%', @TEXTO)
END
SELECT @TEXTO AS NUMERO
Resultado:
Trabalhando com Datas (Listagem de Meses para uma caixa de seleção)
Usando SQL-Server, segue código que nos retornará uma listagem dos Meses
Resultado
SET LANGUAGE 'Brazilian'
DECLARE @MESES TABLE (ID VARCHAR(2), MES VARCHAR(10))
DECLARE @NUM INT
SET @NUM = 0
WHILE @NUM < 12
BEGIN
INSERT INTO @MESES
SELECT REPLICATE('0',2-LEN(CAST(@NUM+1 AS VARCHAR(2))))+
CONVERT(VARCHAR,@NUM+1)
,DATENAME(MONTH,DATEADD(MONTH, @NUM, 0))
SET @NUM = @NUM + 1
END
SELECT * FROM @MESES
Resultado
Assinar:
Postagens (Atom)