SQL Commands

Date as 30-06-2020

SELECT CONVERT(VARCHAR, GETDATE(), 105)

DATE_FORMAT(issue_date , '%d-%m-%Y')

Check for occurrences in the last x years

DATEADD(yy, -3, GETDATE()) -- For the last three years

Case

CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END

Order by specific list

ORDER BY
   CASE purpose
      WHEN 'Main'      THEN 1
      WHEN 'Auxiliary' THEN 2
      WHEN 'Emergency' THEN 3
      ELSE 4 
   END DESC

Columns for multiple years

CAST(SUM(CASE WHEN FORMAT(Orderdatum, 'yyyy') = (YEAR(GetDate()) -5) THEN f.Aantal ELSE 0 END) AS INT) AS "2015",
CAST(SUM(CASE WHEN FORMAT(Orderdatum, 'yyyy') = (YEAR(GetDate()) -4) THEN f.Aantal ELSE 0 END) AS INT) AS "2016",
CAST(SUM(CASE WHEN FORMAT(Orderdatum, 'yyyy') = (YEAR(GetDate()) -3) THEN f.Aantal ELSE 0 END) AS INT) AS "2017",
CAST(SUM(CASE WHEN FORMAT(Orderdatum, 'yyyy') = (YEAR(GetDate()) -2) THEN f.Aantal ELSE 0 END) AS INT) AS "2018",
CAST(SUM(CASE WHEN FORMAT(Orderdatum, 'yyyy') = (YEAR(GetDate()) -1) THEN f.Aantal ELSE 0 END) AS INT) AS "2019",
CAST(SUM(CASE WHEN FORMAT(Orderdatum, 'yyyy') = (YEAR(GetDate()) -0) THEN f.Aantal ELSE 0 END) AS INT) AS "2020",
CAST(SUM(f.Aantal) AS INT) AS "Cum",
CAST(SUM(f.Prijs * f.Aantal) AS INT) AS "Omzet"

Search Case Sensitive

SELECT 1
FROM dbo.Customers
WHERE   CustID LIKE '%HP-IOT%' COLLATE SQL_Latin1_General_CP1_CS_AS

Percentage of an item

AVG(CASE WHEN t.id is null THEN 1.0 ELSE 0 END)