Matt Dalesio dalmat36

## Offset Fetch Filter
/*OFFSET FETCH Filter
Offset by 5 and filter next 5 rows*/
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET  5 ROWS FETCH NEXT 5 ROWS ONLY;

## RowNumber Function
/*ROW_NUMBER() Window function assigns
unique incrementing integers to rows */
SELECT orderid, custid, val, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;

## CONCAT Function
/*CONCAT  function (2012)
Handles replacing nulls*/
Use TSQL2012
SELECT  custid, country, region, city,
CONCAT(country, N',' + region, N',' + city)
AS location
FROM Sales.Customers;

## FORMAT Function
/*FORMAT Function
formats a number as a 10-digit string with leading zeros*/
SELECT FORMAT(1759, 'd10') AS Cust_string;

/*formats a currency to various locale-aware strings*/
SELECT FORMAT(5,'C','en-us') AS US_Value; --US
SELECT FORMAT(5,'C','de-de') AS DE_Value; --Germany

## DATEFROMPARTS Function
/*DATEFROMPARTS
example returns the a date using provide parts and input*/
SELECT DATEFROMPARTS(2014,02,28) AS NewDate;

## EOMONTH Function
/*EOMONTH Function
Example returns the last day of 06/01/2014*/
SELECT EOMONTH('06/01/2014') AS EndofMonth;

/*Example returns the last day of 06/01/2014 + 6months*/
SELECT EOMONTH('06/01/2014',6) AS EndofSixthMonth;

## IIF Function
/*Example IIF function*/
DECLARE @a int = 65, @b int = 25;
SELECT IIF (@a < @b, 'Yes', 'No') AS Result;

## CHOOSE Function
/*Example Choose function*/
SELECT CHOOSE ( 4, 'Option 1', 'Option 2', 'Option 3', 'Option 4' ) AS Result;

## ROW_NUMBER
/*ROW_NUMBER() Window function assigns
unique incrementing integers to rows */
SELECT orderid, custid, val, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;

## BCHS-Footer-3
<!DOCTYPE html>
<html>
    <head>
        <title></title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.0/css/bootstrap.min.css">
    </head>
    <body>
        <div class="container">
        <div class="row footer_top well">
	/*OFFSET FETCH Filter
	Offset by 5 and filter next 5 rows*/
	SELECT orderid, orderdate, custid, empid
	FROM Sales.Orders
	ORDER BY orderdate, orderid
	OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
	/*ROW_NUMBER() Window function assigns
	unique incrementing integers to rows */
	SELECT orderid, custid, val, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY val) AS rownum
	FROM Sales.OrderValues
	ORDER BY custid, val;
	/*CONCAT function (2012)
	Handles replacing nulls*/
	Use TSQL2012
	SELECT custid, country, region, city,
	CONCAT(country, N',' + region, N',' + city)
	AS location
	FROM Sales.Customers;
	/*FORMAT Function
	formats a number as a 10-digit string with leading zeros*/
	SELECT FORMAT(1759, 'd10') AS Cust_string;

	/formats a currency to various locale-aware strings/
	SELECT FORMAT(5,'C','en-us') AS US_Value; --US
	SELECT FORMAT(5,'C','de-de') AS DE_Value; --Germany
	/*DATEFROMPARTS
	example returns the a date using provide parts and input*/
	SELECT DATEFROMPARTS(2014,02,28) AS NewDate;
	/*EOMONTH Function
	Example returns the last day of 06/01/2014*/
	SELECT EOMONTH('06/01/2014') AS EndofMonth;

	/Example returns the last day of 06/01/2014 + 6months/
	SELECT EOMONTH('06/01/2014',6) AS EndofSixthMonth;
	/Example IIF function/
	DECLARE @a int = 65, @b int = 25;
	SELECT IIF (@a < @b, 'Yes', 'No') AS Result;
	/Example Choose function/
	SELECT CHOOSE ( 4, 'Option 1', 'Option 2', 'Option 3', 'Option 4' ) AS Result;