SQL hakcs

sql_hacks.md

Vanilla

  --- Common table expression on the fly
  WITH cte_usuario AS (
    SELECT * FROM (
      VALUES  (1,GETDATE(),'Pessoa 1')
      ,       (2,GETDATE(),'Pessoa 2')
    ) a(id,created_at,name)
  ) SELECT * FROM cte_usuario CTU

DATE GENERATOR

SQL SERVER

-- Gera datas a partir de uma data inicial
-- SQL server
WITH cte_dates(n,dt) 
AS (
    SELECT 
	1 AS n,
		DATEADD(day,1, CAST('20190101' AS DATE)) as dt
    UNION ALL
    SELECT   n+1, 
	     DATEADD(day,1, dt)
    FROM    
	cte_dates
    WHERE n < 365
)
SELECT 
    *
FROM 
    cte_dates
ORDER BY
    dt ASC
OPTION (MAXRECURSION 5000);

SQL SERVER

/*SQL SERVER -- CONVERT TO DATE*/
select CONVERT(DATE,STUFF(STUFF('23072009',5,0,'/'),3,0,'/')  , 103) 

SELECT
      m.maskid
    , m.maskname
    , m.schoolid
    , s.schoolname
    , maskdetail = STUFF(( 
          SELECT ',' + md.maskdetail
          FROM dbo.maskdetails md
          WHERE m.maskid = md.maskid
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.tblmask m
JOIN dbo.school s ON s.ID = m.schoolid
ORDER BY m.maskname

SQL SERVER FUNCTIONS

Divide texto por delimitador

CREATE FUNCTION [dbo].[fnSplit]
(
@String NVARCHAR(max)
,@Delimiter NVARCHAR(10)
)
RETURNS @Result TABLE (dsc_valor NVARCHAR(MAX), chv_item BIGINT)
AS 
BEGIN

DECLARE @x XML 
SELECT
@x = CAST('<A>' + REPLACE(@String, @Delimiter, '</A><A>') + '</A>' AS XML)

INSERT INTO @Result
SELECT
t.value('.', 'NVARCHAR(MAX)') As dsc_valor
, chv_item = ROW_NUMBER() OVER (ORDER BY t.value('count(.)', 'bigint'))
FROM
@x.nodes('/A') AS x (t)

RETURN
END

Corrige um valor por indicador

/*
SELECT [pub].[fnCorrigeValor] (400,'2.4,3.2')
*/

CREATE	FUNCTION [pub].[fnCorrigeValor]
		(@vlr 	AS NUMERIC(18,2), @ind AS VARCHAR(MAX))
RETURNS		VARCHAR(110)
AS
BEGIN
DECLARE @valReturn AS NUMERIC(24,2);

;WITH cte_indicador AS (

	SELECT	chv_item	AS id
	,		dsc_valor	AS val_ind
	FROM [dbo].[fnSplit] (@ind,',')
	
), cte_number AS (
    SELECT	UP.n
	,		CAST(UP.vlr AS decimal(24,2)) vlr
	,		IND.val_ind
	,		 CAST(round(UP.vlr + UP.vlr * (IND.val_ind / 100.00),2)  AS decimal(24,2)) AS mult

	FROM (
		SELECT 1 AS n
		--, CAST(@vlr AS NUMERIC(18,2))  AS vlr
		, CAST(@vlr AS decimal(24,2))  AS vlr
	) UP (n, vlr)
	INNER JOIN cte_indicador IND
		ON IND.id = UP.n
	
	UNION ALL
	
	SELECT	a.n
	,		a.vlr
	,		IND.val_ind
	,		CAST(a.mult + a.mult * (IND.val_ind / 100.00) AS decimal(24,2)) AS mult
	FROM (
		SELECT	n + 1
		--,		CAST(vlr AS NUMERIC(18,2)) * CAST(2.0 AS NUMERIC(18,2))
		,	vlr 
		,	val_ind 
		,	mult
		FROM cte_number
		WHERE n <  5.0
	) a (n, vlr, val_ind, mult)
	INNER JOIN cte_indicador IND
		ON IND.id = a.n
)
SELECT @valReturn = (SELECT mult FROM cte_number WHERE n = (SELECT MAX(n) FROM cte_number)) 

 RETURN CASE WHEN @valReturn > 0 THEN @valReturn ELSE @vlr END
END

Retorna digitos de uma string

CREATE	FUNCTION [pub].[fnRetornaDigito]
		(@str 	 	VARCHAR(110))
RETURNS		VARCHAR(110)
AS
BEGIN

	DECLARE @strReturn AS VARCHAR(110);
	--INSERT INTO @ReturnVal(c) VALUES('a');

	;WITH cte_number AS (
		SELECT 1 AS n
		UNION ALL
		SELECT	n + 1 AS n
		FROM cte_number
		WHERE n <  LEN(@str)
	), cte_tratada AS (
		SELECT * FROM 
		(
			SELECT n
			,	SUBSTRING(@str,n,1)				AS c
			FROM cte_number

			WHERE n <= LEN(@str)
		)tbC(n,c)
		WHERE PATINDEX('%[0-9]%', c) = 1
		AND ISNUMERIC(c) = 1

	) --SELECT * FROM cte_tratada

	 SELECT @strReturn = (SELECT c FROM cte_tratada WHERE n = (SELECT MIN(n) FROM cte_tratada)) 
			+ STUFF(
				 (SELECT '' + c FROM cte_tratada 
				    FOR XML PATH (''), TYPE
					).value('.', 'VARCHAR(110)')
			 , 1, 1, '')
	
	RETURN CASE WHEN LEN(LTRIM(RTRIM(@strReturn))) > 0 THEN @strReturn ELSE NULL END
END