Skip to content

Instantly share code, notes, and snippets.

View dalmat36's full-sized avatar

Matt Dalesio dalmat36

1 follower · 9 following
View GitHub Profile
@dalmat36
dalmat36 / Offset Fetch Filter
Last active August 29, 2015 13:57
/*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;
@dalmat36
dalmat36 / RowNumber Function
Created March 11, 2014 15:29
/*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;
@dalmat36
dalmat36 / CONCAT Function
Created March 15, 2014 15:24
SQL Server 2012 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;
@dalmat36
dalmat36 / FORMAT Function
Created March 15, 2014 15:40
SQL Server 2012 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
@dalmat36
dalmat36 / DATEFROMPARTS Function
Last active August 29, 2015 13:57
SQL Server 2012 DATEFROMPARTS Function
/*DATEFROMPARTS
example returns the a date using provide parts and input*/
SELECT DATEFROMPARTS(2014,02,28) AS NewDate;
@dalmat36
dalmat36 / EOMONTH Function
Last active August 29, 2015 13:57
SQL Server 2012 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;
@dalmat36
dalmat36 / IIF Function
Created March 15, 2014 16:20
SQL Server 2012 IIF Function
/*Example IIF function*/
DECLARE @a int = 65, @b int = 25;
SELECT IIF (@a < @b, 'Yes', 'No') AS Result;
@dalmat36
dalmat36 / CHOOSE Function
Created March 15, 2014 17:10
SQL Server 2012 CHOOSE Function
/*Example Choose function*/
SELECT CHOOSE ( 4, 'Option 1', 'Option 2', 'Option 3', 'Option 4' ) AS Result;
@dalmat36
dalmat36 / ROW_NUMBER
Last active August 29, 2015 13:57
Multiple Gists
/*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;
@dalmat36
dalmat36 / BCHS-Footer-3
Created March 30, 2014 18:49
Revision #3 of the Blair County Historical Society homepage footer