Common window functions

Intermediate SQL Server

Ginger Grant

Instructor

FIRST_VALUE() and LAST_VALUE()

FIRST_VALUE() returns the first value in the window
LAST_VALUE() returns the last value in the window

FIRST_VALUE

FIRST_VALUE() and LAST_VALUE() in T-SQL

Note that for FIRST_VALUE and LAST_VALUE the ORDER BY command is required

-- Select the columns
SELECT SalesPerson, SalesYear, CurrentQuota, 
    -- First value from every window
       FIRST_VALUE(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS StartQuota, 
    -- Last value from every window 
       LAST_VALUE(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS EndQuota, 
       ModifiedDate as ModDate
FROM SaleGoal

Results

+------------+----------+------------+----------+----------+----------+
|SalesPerson |SalesYear |CurrentQuota|StartQuota| EndQuota |ModDate   |
+------------+----------+------------+----------+----------+----------+
|Bob         |2011      |28000.00    |28000.00  |91000.00  |2011-04-16|
|Bob         |2011      |7000.00     |28000.00  |91000.00  |2011-07-17| 
|Bob         |2011      |91000.00    |28000.00  |91000.00  |2011-10-17|
|Bob         |2012      |140000.00   |140000.00 |107000.00 |2012-01-15|
|Bob         |2012      |70000.00    |140000.00 |107000.00 |2012-04-15|
|Bob         |2012      |154000.00   |140000.00 |107000.00 |2012-07-16|
|Bob         |2012      |107000.00   |140000.00 |107000.00 |2012-10-16|
...
+------------+----------+------------+----------+----------+----------+

Getting the next value with LEAD()

Provides the ability to query the value from the next row
NextQuota column is created by using LEAD()
Requires the use of ORDER BY to order the rows

LEAD() in T-SQL

SELECT SalesPerson, SalesYear, CurrentQuota, 
-- Create a window function to get the values from the next row
       LEAD(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS NextQuota, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|NextQuota   | ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |367000.00   |2011-04-15|
|Mary        |2011      |367000.00   |556000.00   |2011-04-16| 
|Mary        |2011      |556000.00   |7000.00     |2011-07-15| 
|Bob         |2011      |7000.00     |NULL        |2011-07-17|
|Bob         |2012      |70000.00    |502000.00   |2012-01-15|
|Mary        |2012      |502000.00   |154000.00   |2012-01-16| 
...
+------------+----------+------------+------------+----------+

Getting the previous value with LAG()

Provides the ability to query the value from the previous row
PreviousQuota column is created by using LAG()
Requires the use of ORDER BY to order the rows

LAG() in T-SQL

SELECT SalesPerson, SalesYear, CurrentQuota, 
-- Create a window function to get the values from the previous row
       LAG(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS PreviousQuota, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+-------------+----------+
|SalesPerson |SalesYear |CurrentQuota|PreviousQuota |ModDate  |
+------------+----------+------------+-------------+----------+
|Bob         |2011      |28000.00    |NULL         |2011-04-15|
|Mary        |2011      |367000.00   |28000.00     |2011-04-16| 
|Mary        |2011      |556000.00   |367000.00    |2011-07-15|
|Bob         |2011      |7000.00.00  |556000.00    |2011-07-17|
|Bob         |2012      |7000.00     |NULL         |2012-01-15|
|Mary        |2012      |502000.00   |7000.00      |2012-01-16|
...
+------------+----------+------------+------------+----------+

Let's practice !

Intermediate SQL Server