Chapter 13) Built-in scalar functions

SELECT
TRY_CAST('PATRICK' AS INT) TryCast,
TRY_CONVERT(DATETIME, '13/2/2999', 112) AS TryConvert,
PARSE('Saturday, 26 May 2012' AS DATETIME USING 'en-US') AS Parse,
TRY_PARSE('Patricks BirthDay' AS DATETIME USING 'en-US') AS TryParse

SELECT
'LEBLANC '+', '+' PATRICK' RawValues,
RTRIM('LEBLANC ')+', '+LTRIM(' PATRICK') TrimValue,
LEFT('PatrickDTomorr', 7) [Left],
RIGHT('DTomorrLeBlanc', 7) [Right],
SUBSTRING('DTomorrPatrick',8,LEN('DTomorrPatrick')) [SubString],
'12/'+CAST(1 AS VARCHAR)+'/2012' WithoutConcat,
CONCAT('12/',1,'/2012') WithConcat

declare @choosevar int = 3
SELECT
CHOOSE(@choosevar, 'ONE', 'TWO', 'PATRICK', 'THREE') [Choose],
IIF(DATENAME(MONTH, GETDATE()) = 'July', 'The 4th is this month', 'No Fireworks') AS [IIF]

CHAPTER 14) Advanced T-SQL topics

USE AdventureWorks2012;
SELECT
SUM(poh.TotalDue) AS TotalDue
FROM Purchasing.PurchaseOrderHeader poh

USE AdventureWorks2012;
SELECT
SUM(poh.TotalDue) AS [Total Due],
AVG(poh.TotalDue) AS [Average Total Due],
COUNT(poh.EmployeeID) [Number Of Employees],
COUNT(DISTINCT poh.EmployeeID) [Distinct Number Of Employees]
FROM Purchasing.PurchaseOrderHeader poh

USE AdventureWorks2012;
SELECT
SUM(poh.TotalDue) AS [Total Due],
AVG(poh.TotalDue) AS [Average Total Due],
COUNT(poh.EmployeeID) [Number Of Employees],
COUNT(DISTINCT poh.EmployeeID) [Distinct Number Of Employees],
COUNT(*) [ROWS OR NUM OF ORDERS],
COUNT(*)/COUNT(DISTINCT poh.EmployeeID) [AVG ORDERS PER EMP],
SUM(poh.TotalDue)/COUNT(DISTINCT poh.EmployeeID) [AVG TOT DUE PER EMP]
FROM Purchasing.PurchaseOrderHeader poh

USE AdventureWorks2012;
SELECT
sm.Name AS ShippingMethod
SUM(poh.TotalDue) AS [Total Due],
AVG(poh.TotalDue) AS [Average Total Due],
COUNT(poh.EmployeeID) [Number Of Employees],
COUNT(DISTINCT poh.EmployeeID) [Distinct Number Of Employees]
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.ShipMethod sm
ON poh.ShipMethodID = sm.ShipMethodID
GROUP BY sm.Name

USE AdventureWorks2012;
SELECT
sm.Name AS ShippingMethod,
YEAR(poh.OrderDate) AS OrderYear,
SUM(poh.TotalDue) AS [Total Due],
AVG(poh.TotalDue) AS [Average Total Due],
COUNT(poh.EmployeeID) AS [Number Of Employees],
COUNT(DISTINCT poh.EmployeeID) AS [Distinct Number Of Employees]
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.ShipMethod sm
ON poh.ShipMethodID = sm.ShipMethodID
GROUP BY
sm.Name,
YEAR(poh.OrderDate)

USE AdventureWorks2012;
SELECT
sm.Name AS ShippingMethod,
YEAR(poh.OrderDate) AS OrderYear,
SUM(poh.TotalDue) AS [Total Due],
AVG(poh.TotalDue) AS [Average Total Due],
COUNT(poh.EmployeeID) AS [Number Of Employees],
COUNT(DISTINCT poh.EmployeeID) AS [Distinct Number Of Employees]
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.ShipMethod sm
ON poh.ShipMethodID = sm.ShipMethodID
GROUP BY
sm.Name,
YEAR(poh.OrderDate)
HAVING SUM(poh.TotalDue) > 5000000

USE AdventureWorks2012
GO
;WITH ProductQty
AS
(
	SELECT TOP(10)
		p.ProductID,
		SUM(OrderQty) AS OrderQty
	FROM Sales.SalesOrderDetail AS sod
	INNER JOIN Production.Product AS p
		ON sod.ProductID = p.ProductID
	GROUP BY p.ProductID
)
SELECT 
	p.NAME AS ProductName,
	pq.OrderQty,
	ROW_NUMBER() OVER(ORDER BY pq.OrderQty DESC) ROWNUMBER,
	RANK() OVER(ORDER BY pq.OrderQty DESC) [RANK],
	DENSE_RANK() OVER(ORDER BY pq.OrderQty DESC) [DENSERANK]
FROM ProductQty AS pq
INNER JOIN Production.Product AS p
	ON pq.ProductID = p.ProductID

SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID

USE AdventureWorks2012;
WITH EmployeePOs (EmployeeID, [Total Due])
AS
(
SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID
)
SELECT *
FROM EmployeePOs

USE AdventureWorks2012;
WITH EmployeePOs (EmployeeID, [Total Due])
AS
(
SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID
)
SELECT ep.EmployeeID, p.FirstName, p.LastName, ep.[Total Due]
FROM EmployeePOs ep
INNER JOIN Person.Person p
     ON ep.EmployeeID = p.BusinessEntityID

USE AdventureWorks2012;
DECLARE @EmployeePOs AS TABLE
(
EmployeeID int,
TotalDue money
)
INSERT INTO @EmployeePOs
SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID

USE AdventureWorks2012;
DECLARE @EmployeePOs AS TABLE
(
EmployeeID int,
TotalDue money
)
INSERT INTO @EmployeePOs
SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID

SELECT
ep.EmployeeID,
p.FirstName,
p.LastName,
ep.[TotalDue]
FROM @EmployeePOs ep
INNER JOIN Person.Person p
ON ep.EmployeeID = p.BusinessEntityID

USE AdventureWorks2012;
CREATE TABLE #EmployeePOs
(
EmployeeID int,
TotalDue money
)
INSERT INTO #EmployeePOs
SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID

USE AdventureWorks2012;
CREATE TABLE #EmployeePOs
(
EmployeeID int,
TotalDue money
)
INSERT INTO #EmployeePOs
SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID

USE AdventureWorks2012;
CREATE TABLE #EmployeePOs
(
EmployeeID int,
TotalDue money
)
INSERT INTO #EmployeePOs
SELECT
poh.EmployeeID,
CONVERT(varchar(20), SUM(poh.TotalDue),1)
FROM Purchasing.PurchaseOrderHeader poh
GROUP BY
poh.EmployeeID

SELECT
ep.EmployeeID,
p.FirstName,
p.LastName,
ep.[TotalDue]
FROM #EmployeePOs ep
INNER JOIN Person.Person p
ON ep.EmployeeID = p.BusinessEntityID

USE AdventureWorks2012;
BEGIN
DECLARE @StartingHireDate datetime = '12/31/2001'
SELECT e.BusinessEntityID, p.FirstName, p.LastName, e.HireDate
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE HireDate <= @StartingHireDate
END

IF(DATENAME(M, GETDATE())='December')
BEGIN
SELECT 'Time for the holidays!!!!' Results
END
ELSE
BEGIN
SELECT 'Not sure what''s going on now :(' Results
END

DECLARE @count int = 0
WHILE (@count < 10)
BEGIN
SET @count = @count + 1;
IF(@count < 5)
BEGIN
SELECT @count AS Counter
CONTINUE;
END
ELSE
BREAK;
END