You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
--- 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 (
SELECT1AS n,
DATEADD(day,1, CAST('20190101'ASDATE)) as dt
UNION ALLSELECT 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*/selectCONVERT(DATE,STUFF(STUFF('23072009',5,0,'/'),3,0,'/') , 103)
SELECTm.maskid
, m.maskname
, m.schoolid
, s.schoolname
, maskdetail = STUFF((
SELECT','+md.maskdetailFROMdbo.maskdetails md
WHEREm.maskid=md.maskid
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROMdbo.tblmask m
JOINdbo.school s ONs.ID=m.schoolidORDER BYm.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)
ASBEGIN
DECLARE @x XML
SELECT
@x = CAST('<A>'+ REPLACE(@String, @Delimiter, '</A><A>') +'</A>'AS XML)
INSERT INTO @Result
SELECTt.value('.', 'NVARCHAR(MAX)') As dsc_valor
, chv_item = ROW_NUMBER() OVER (ORDER BYt.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 ASNUMERIC(18,2), @ind ASVARCHAR(MAX))
RETURNS VARCHAR(110)
ASBEGIN
DECLARE @valReturn ASNUMERIC(24,2);
;WITH cte_indicador AS (
SELECT chv_item AS id
, dsc_valor AS val_ind
FROM [dbo].[fnSplit] (@ind,',')
), cte_number AS (
SELECTUP.n
, CAST(UP.vlrASdecimal(24,2)) vlr
, IND.val_ind
, CAST(round(UP.vlr+UP.vlr* (IND.val_ind/100.00),2) ASdecimal(24,2)) AS mult
FROM (
SELECT1AS n
--, CAST(@vlr AS NUMERIC(18,2)) AS vlr
, CAST(@vlr ASdecimal(24,2)) AS vlr
) UP (n, vlr)
INNER JOIN cte_indicador IND
ONIND.id=UP.nUNION ALLSELECTa.n
, a.vlr
, IND.val_ind
, CAST(a.mult+a.mult* (IND.val_ind/100.00) ASdecimal(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
ONIND.id=a.n
)
SELECT @valReturn = (SELECT mult FROM cte_number WHERE n = (SELECTMAX(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)
ASBEGIN
DECLARE @strReturn ASVARCHAR(110);
--INSERT INTO @ReturnVal(c) VALUES('a');
;WITH cte_number AS (
SELECT1AS n
UNION ALLSELECT n +1AS 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) =1AND ISNUMERIC(c) =1
) --SELECT * FROM cte_tratadaSELECT @strReturn = (SELECT c FROM cte_tratada WHERE n = (SELECTMIN(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