Kumpulan Rumus SQL Server

--FORMAT TANGGAL DAN BULAN STATIC :
set @datglfaktur=DATEFROMPARTS (YEAR(getdate()), 12, 31)
=========================
--PILIH SATU ROW :

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY cardcode ORDER BY cardcode DESC) AS rn
   FROM #tmp2
)
SELECT *
FROM cte
WHERE rn = 1
========================
--menghilangkan karakter spasi dan enter

REPLACE(REPLACE(T1.U_KETERANGAN, CHAR(13), ''), CHAR(10), '') as [U_KETERANGAN]

========================
--cek tabel yang terpakai atau tidak terpakai di SP

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tmp_tes_cekgiro%'
========================
--cari data yang tidak mapping di table

  FROM [TMP_PROCESS].[dbo].[M_IDAS_CUSTOMER] a
where NOT EXISTS (SELECT * FROM [DLS].[dbo].[M_IDAS_CUSTOMER] i where i.chKdCustomer = a.chKdCustomer)

INSERT INTO DLS.[dbo].[M_IDAS_SITE] SELECT *
  FROM [TMP_PROCESS].[dbo].[M_IDAS_SITE] a
where NOT EXISTS (SELECT * FROM DLS.[dbo].[M_IDAS_SITE]  i where i.CHKDSITE = a.CHKDSITE)

========================
--ambil awal bulan

DECLARE @XTGL  DATETIME
declare @test datetime
SET @XTGL = GETDATE()-1
 set @test=(DATEADD(MONTH, DATEDIFF(MONTH, 0, @XTGL), 0))

select @test

========================
--cari nilai yang duplikat
select  [no je],count(*) from #TMP3 group by [no je] having count(*) >1

========================
--PIVOT

select * from #tmp1
pivot (avg (TOTAL_ROW) for  Tanggal in ([1],[2],[3])) as pvt


========================
--AMBIL VALUE TAHUN (YY)

SELECT GETDATE(),LEFT(CONVERT(VARCHAR, GETDATE(), 12), 2) ,CONVERT(VARCHAR, GETDATE(), 12)
FROM TR_BUDREL_H WHERE CMARDate IS NOT NULL

========================
--ORDER BY DALAM UNION
PAKAI SUBQUERY
ROW_NUMBER() OVER (ORDER BY B.CMAPNoBySys DESC) AS RowNum,

========================
--cast nvarchar to numeric
SELECT [KODE CABANG],SUM (CAST (ROUND([SLO VAL FAKTUR],2) AS DECIMAL(18,2)))TOTAL FROM TABLE
GROUP BY [KODE CABANG]