Tutorial by Examples

Table Setup CREATE TABLE dbo.Employees ( EmployeeID INT NOT NULL PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, ManagerID INT NULL ) GO INSERT INTO Employees VALUES (101, 'Ken', 'Sánchez', NULL) INSERT INTO Employees VALUES (102, 'Keith', ...

Employees table : | ID | FirstName | LastName | Gender | Salary | +------+-----------+----------+--------+--------+ | 1 | Jahangir | Alam | Male | 70000 | | 2 | Arifur | Rahman | Male | 60000 | | 3 | Oli | Ahammed | Male | 45000 | | 4 | Sima | Sulta...

Employees table : | ID | FirstName | LastName | Gender | Salary | +------+-----------+----------+--------+--------+ | 1 | Mark | Hastings | Male | 60000 | | 1 | Mark | Hastings | Male | 60000 | | 2 | Mary | Lambeth | Female | 30000 | | 2 | Mary | Lambe...

DECLARE @startdate CHAR(8), @numberDays TINYINT SET @startdate = '20160101' SET @numberDays = 10; WITH CTE_DatesTable AS ( SELECT CAST(@startdate as date) AS [date] UNION ALL SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [date]) <= DateAdd(DAY, @nu...

This example shows how to get every year from this year to 2011 (2012 - 1). WITH yearsAgo ( myYear ) AS ( -- Base Case: This is where the recursion starts SELECT DATEPART(year, GETDATE()) AS myYear UNION ALL -- This MUST be UNION ALL (cannot be UNION) -- Recurs...

;WITH cte_query_1 AS ( SELECT * FROM database.table1 ), cte_query_2 AS ( SELECT * FROM database.table2 ) SELECT * FROM cte_query_1 WHERE cte_query_one.fk IN ( SELECT PK FROM cte_query_2 ) With common table expressions, it is possible to create multiple ...