Continuing on the TSQL 2012 Analytic Series now on to the CUME_DIST function SQL Server 2012 introduces another new analytic function. The Cumulative Distribution Function… Read More »More TSQL 2012 Analytics CUME_DIST – Cumulative Distribution
Like the other new Analytic functions, PERCENTILE_DISC and PERCENTILE_CONT require the use of the OVER clause. For this example I will be using almost the… Read More »More TSQL 2012 Analytics PERCENTILE_DISC and PERCENTILE_CONT
Percent rank is defined as the number of values that are the same or less than the current value divided by one less than the… Read More »More TSQL Analytic Functions – PERCENT_RANK
SQL Server 2012 introduces 8 new analytic functions. This post will cover 2 of them LEAD and LAG, which can be used to reference a… Read More »TSQL Analytic Functions LEAD and LAG
In a previous article I covered the usage of ROWS PRECEDING and FOLLOWING in the over clause. For this example I am going to use the same database and tables that I created in the previous example to show ROWS UNBOUNDED both PRECEEDING and FOLLOWING.
One of the new features available in TSQL in SQL Server 2012 is the ROWS UNBOUNDED PRECEDING and the ROWS UNBOUNDED FOLLOWING options. The UNBOUNDED option in available when using the PRECEDING and FOLLOWING options. Here’s how they work…
-- ROWS UNBOUNDED PRECEDING select Year, DepartmentID, Revenue, min(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS UNBOUNDED PRECEDING) as MinRevenueToDate from REVENUE order by departmentID, year;
In this example, the MinRevenueToDate lists the lowest revenue for this row and all earlier rows ordered by date in the current department id.
You can see that row 1 sets the MinRevenueToDate to 10030, and it doesn’t change until row 7 with a lower revenue year.
Read More »Transact SQL OVER Clause – ROWS UNBOUNDED PRECEDING or FOLLOWING
With SQL Server versions 2008R2 and 2012, you can access the registry to get the settings for the current instance of SQL Server. Here is how… Read More »Accessing the registry from TSQL on SQL Server 2012 and 2008R2
First for this example, and a few to follow we need to create the database that we are going to play around in. USE [Master];… Read More »ROWS PRECEDING and FOLLOWING in TSQL 2012