Page 93 - SQL

SELECT statement, you can compare values in the current row with values in the following row.

You specify the values that should be compared using a scalar expression. The offset parameter
is the number of rows after the current row that will be used in the comparison.

You specify the value that should be returned when the expression at offset has a NULL value using
the default parameter. If you don't specify these parameters, the default of one row is used and a
value of NULL is returned.

SELECT BusinessEntityID, SalesYTD,
LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

This example uses the LEAD and LAG functions to compare the sales values for each employee
to date with those of the employees listed above and below, with records ordered based on the
BusinessEntityID column.

BusinessEntityID SalesYTD Lead value Lag value

274 559697.5639 3763178.1787 0.0000

275 3763178.1787 4251368.5497 559697.5639

276 4251368.5497 3189418.3662 3763178.1787

277 3189418.3662 1453719.4653 4251368.5497

278 1453719.4653 2315185.6110 3189418.3662

279 2315185.6110 1352577.1325 1453719.4653

PERCENT_RANK and CUME_DIST

The PERCENT_RANK function calculates the ranking of a row relative to the row set. The percentage is
based on the number of rows in the group that have a lower value than the current row.

The first value in the result set always has a percent rank of zero. The value for the highest-ranked
– or last – value in the set is always one.

The CUME_DIST function calculates the relative position of a specified value in a group of values, by
determining the percentage of values less than or equal to that value. This is called the cumulative
distribution.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
AS "Cumulative Distribution"

https://riptutorial.com/ 75