My SQL cheatsheet

1.
IF OBJECT_ID('dbo.TABLE') IS NOT NULL
 BEGIN
  DROP TABLE dbo.TABLE
 END;
--------------------------------------------------
2.
ROW_NUMBER() OVER(PARTITION BY MONTHID
      ORDER BY DATEID
     ) AS TranOrder
-------------------------------------------------
3.
;WITH A AS (
SELECT .....
FROM ........ WITH (NOLOCK)
WHERE .........
)
,B AS (
SELECT ....
 FROM ....
WHERE .....
)
SELECT A.*, B.*
FROM ......
-----------------------------------------------
4.
IF OBJECT_ID('dbo.Object_choosen') IS NOT NULL
 BEGIN
  DROP TABLE dbo.Object_choosen
  END;
GO

SELECT TOP 50000 ID
 INTO dbo.Object_choosen
 FROM dbo.Object_left
;
.........................................
DELETE FROM dbo.Object_left
WHERE ID IN (SELECT * FROM dbo.Object_choosen)

-----------------------------------------------------

5. choose ID in A not in B
SELECT TOP 10 A.ID
FROM A
LEFT JOIN B WITH (NOLOCK)
ON A.ID = B.ID
WHERE B.ID IS NULL
------------------------------------------------------
6. change table
ALTER TABLE A
ADD ID int
ALTER TABLE A
DROP COLUMN ID
UPDATE A
SET A.NUM = B.NUM
from DATA1 A
LEFT JOIN DATA2 B
 ON A.ID = B.ID
UPDATE DATA
SET LENGTH = DATEDIFF(DAY, DT_EARLY, DT_LATE)
-------------------------------------------------------------------------
7. bucket

DECLARE @CNT FLOAT
SET @CNT = (SELECT COUNT(*) FROM #V)
     
SELECT BUCKET
 ,SCORE_AVG = AVG(SCORE)
 ,Y_RATE = AVG(CONVERT(FLOAT, Y))
 ,NUM = COUNT(*)
FROM (SELECT BUCKET = CEILING(ROW_NUMBER() OVER (ORDER BY SCORE)/@CNT*20)/20
   ,*
   FROM #V
   ) TEMP
GROUP BY BUCKET
ORDER BY BUCKET
---------------------------------------------------------------------------------------------------

8. percentile
/****************  Main Category  *******************************/
WITH SourceData AS (
    --Table of sets over which we want to produce multiple quartiles.
     SELECT obj_ID, DtID, FLAG_1, ID
    FROM ..........
)
/*****************  Percentile  ******************************/
,  Percentile AS (
SELECT  ID
  ,MAX(P1Val) P1
  ,MAX(P5Val) P5
  ,MAX(P10Val) P10
   ,MAX(P25Val) P25
  ,MAX(P50Val) P50
  ,MAX(P75Val) P75
  ,MAX(P80Val) P80
  ,MAX(P85Val) P85
  ,MAX(P90Val) P90
  ,MAX(P95Val) P95
  ,MAX(P99Val) P99
FROM (
    --Expose the detail values for only the records at the index values
    --generated by the summary subquery. All other values are left as NULL.
    SELECT detail.ID,
     CASE WHEN RowNum = P1Idx THEN DtID ELSE NULL END P1Val,
 CASE WHEN RowNum = P5Idx THEN DtID ELSE NULL END P5Val,
  CASE WHEN RowNum = P10Idx THEN DtID ELSE NULL END P10Val,
 CASE WHEN RowNum = P25Idx THEN DtID ELSE NULL END P25Val,
 CASE WHEN RowNum = P50Idx THEN DtID ELSE NULL END P50Val,
 CASE WHEN RowNum = P75Idx THEN DtID ELSE NULL END P75Val,
  CASE WHEN RowNum = P80Idx THEN DtID ELSE NULL END P80Val,
 CASE WHEN RowNum = P85Idx THEN DtID ELSE NULL END P85Val,
 CASE WHEN RowNum = P90Idx THEN DtID ELSE NULL END P90Val,
 CASE WHEN RowNum = P95Idx THEN DtID ELSE NULL END P95Val,
  CASE WHEN RowNum = P99Idx THEN DtID ELSE NULL END P99Val
    FROM
        --Calculate a row number sorted by [OpsDtID] for each group.
        (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID
          ORDER BY DtID
           ) RowNum
        FROM SourceData) AS detail
    INNER JOIN (
        --Summarize to find index numbers and fractions we need to use to locate
        --the values at the quartile points.
        SELECT ID,
  --Calculate percentiles based on Nearest Rank
 FLOOR(ROUND(COUNT(*)*1/100.0 + 1/2.0, 0)) P1Idx,
 FLOOR(ROUND(COUNT(*)*5/100.0 + 1/2.0, 0)) P5Idx,
 FLOOR(ROUND(COUNT(*)*10/100.0 + 1/2.0, 0)) P10Idx,
 FLOOR(ROUND(COUNT(*)*25/100.0 + 1/2.0, 0)) P25Idx,
  FLOOR(ROUND(COUNT(*)*50/100.0 + 1/2.0, 0)) P50Idx,
 FLOOR(ROUND(COUNT(*)*75/100.0 + 1/2.0, 0)) P75Idx,
 FLOOR(ROUND(COUNT(*)*80/100.0 + 1/2.0, 0)) P80Idx,
 FLOOR(ROUND(COUNT(*)*85/100.0 + 1/2.0, 0)) P85Idx,
 FLOOR(ROUND(COUNT(*)*90/100.0 + 1/2.0, 0)) P90Idx,
  FLOOR(ROUND(COUNT(*)*95/100.0 + 1/2.0, 0)) P95Idx,
 FLOOR(ROUND(COUNT(*)*99/100.0 + 1/2.0, 0)) P99Idx
        FROM SourceData
        GROUP BY ID
        HAVING COUNT(*) > 1
   
    ) AS summary
    ON detail.ID  = summary.ID
) AS combined
GROUP BY ID
)

/****************  Basic Statistic Summary  ***********************/
, Summary AS (
SELECT ID
  ,ClosingRatio = (CASE
         WHEN COUNT(DISTINCT obj_ID) = 0 THEN NULL
          ELSE CAST(SUM(FLAG_1) AS DECIMAL(15,5))*100/COUNT(DISTINCT obj_ID)
     END
     )
  ,MIN(DtID) MIN
  ,MAX(DtID) MAX
  ,AVG(DtID) AVG
  ,CAST(COUNT(DISTINCT obj_ID)*100/
   (SELECT CAST(COUNT(DISTINCT obj_ID) AS DECIMAL(15,5))
     FROM SourceData
    )AS DECIMAL(15,5)
    ) AS PCT
 ,COUNT(DISTINCT obj_ID) AS Count
 FROM SourceData
 GROUP BY ID
)
/**************  Results  ******************************************/

SELECT B.ID,B.ClosingRatio
  ,PCT
  ,P1
  ,P5
  ,P10
  ,P25
  ,P50
  ,P75
  ,P80
  ,P85
  ,P90
  ,P95
  ,P99
  ,MIN
  ,AVG
  ,MAX
  ,Count
FROM Percentile AS A
 FULL JOIN Summary AS B
ON A.ID  = B.ID
ORDER BY B.ID
---------------------------------------------------------------------------------