Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL, WINDOW Functions | (Partition, order, frame), https://www.youtube…
SQL
-
-
Row Level Function (Single Row) (Transform, Analyse, clean,Manipulate)
STRING Function
-
-
Extraction
-
-
SUBSTRING (SUBSTRING(TRIM(First_name), 2, LEN(FIRST_NAME))
-
DATE & TIME Function
Part Extraction
-
-
-
DATEPART (Specific part in no.) Week/Quarter-DATEPART (Month, OrderDate)
DATENAME - Name of specific part of Date-MonthName-DATENAME(month,CreationTime) Month
DATETRUNC-Truncate date to specific part -DATETRUNC(minute, CreationTime) Minute)
-
-
Format & Casting
FORMAT- FORMAT(CreationTime, dd/ddd/dddd)
-
-
Calculations
DATEADD - DATEADD(year, 2, OrderDate) As TwoYear
DATEDIFF -DATEDIFF(year, BirthDate, GETDATE()) Age
-
-
NULL Function- AVG(COALESCE(Score,0)) OVER() AvgScore - Consider NULL as 0
CASE Function -CASE when Score IS NULL THEN 1 ELSE 0 END FLag, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ....ELSE result END
-
-
-
-
-
-
-
-
-
-
WINDOW Functions | (Partition, order, frame)
-
-
-
-