SQL Server 2022 introduced a powerful enhancement to the LAG and LEAD functions with the IGNORE NULLS option. This feature allows for more precise analysis and reporting by skipping over NULL values in data sets. In this blog, we’ll explore how to use these functions effectively using the JBDB database, and we’ll demonstrate their application with a detailed business use case.
Business Use Case: Sales Data Analysis
Imagine a retail company, JBStore, that wants to analyze its sales data to understand sales trends better. They aim to compare each month’s sales with the previous and next months, ignoring any missing data (represented by NULL values). This analysis will help identify trends and outliers, aiding in better decision-making.
Setting Up the JBDB Database
First, let’s set up the JBDB database and create a SalesData table with some sample data, including NULL values to represent months with no sales data.
-- Create JBDB database
CREATE DATABASE JBDB;
GO
-- Use the JBDB database
USE JBDB;
GO
-- Create SalesData table
CREATE TABLE SalesData (
SalesMonth INT,
SalesAmount INT
);
-- Insert sample data, including NULLs
INSERT INTO SalesData (SalesMonth, SalesAmount)
VALUES
(1, 1000),
(2, 1500),
(3, NULL),
(4, 1800),
(5, NULL),
(6, 2000);
GOLAG and LEAD Functions: A Quick Recap
The LAG function allows you to access data from a previous row in the same result set without the use of a self-join. Similarly, the LEAD function accesses data from a subsequent row. Both functions are part of the SQL window functions family and are particularly useful in time series analysis.
Using LAG and LEAD with IGNORE NULLS
The IGNORE NULLS option is a game-changer, as it allows you to skip over NULL values, providing more meaningful results. Here’s how you can use it with the LAG and LEAD functions:
Example 1: LAG Function with IGNORE NULLS
SELECT
SalesMonth,
SalesAmount,
LAG(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS PreviousMonthSales
FROM
SalesData;In this example, LAG(SalesAmount, 1) IGNORE NULLS retrieves the sales amount from the previous month, skipping over any NULL values.
Example 2: LEAD Function with IGNORE NULLS
SELECT
SalesMonth,
SalesAmount,
LEAD(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS NextMonthSales
FROM
SalesData;Here, LEAD(SalesAmount, 1) IGNORE NULLS retrieves the sales amount from the next month, again skipping over NULL values.
Practical Example: Analyzing Sales Trends
Let’s combine these functions to analyze sales trends more effectively.
SELECT
SalesMonth,
SalesAmount,
LAG(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS PreviousMonthSales,
LEAD(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS NextMonthSales
FROM
SalesData;This query provides a complete view of each month’s sales, the previous month’s sales, and the next month’s sales, excluding any NULL values. This is incredibly useful for identifying patterns, such as periods of growth or decline.
Detailed Business Use Case: Data-Driven Decision Making
By utilizing the IGNORE NULLS option with LAG and LEAD functions, JBStore can:
- Identify Growth Periods: Detect months where sales increased significantly compared to the previous or next month.
- Spot Anomalies: Easily identify months with unusually high or low sales, excluding months with missing data.
- Trend Analysis: Understand longer-term trends by comparing sales over multiple months.
These insights can inform marketing strategies, inventory planning, and more.
Calculate Difference Between Current and Previous Month’s Sales:
SELECT SalesMonth, SalesAmount, SalesAmount - LAG(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS SalesDifference FROM SalesData;Identify Months with Sales Decrease Compared to Previous Month:
WITH CTE AS (
SELECT
SalesMonth,
SalesAmount,
LAG(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS PreviousMonthSales
FROM
SalesData
)
SELECT
SalesMonth,
SalesAmount,
PreviousMonthSales
FROM
CTE
WHERE
SalesAmount < PreviousMonthSales;
Find the Second Previous Month’s Sales:
SELECT SalesMonth, SalesAmount, LAG(SalesAmount, 2) IGNORE NULLS OVER (ORDER BY SalesMonth) AS SecondPreviousMonthSales FROM SalesData;Calculate the Rolling Average of the Last Two Months (Ignoring NULLs):
SELECT SalesMonth, SalesAmount, (SalesAmount + LAG(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth)) / 2 AS RollingAverage FROM SalesData;Compare Sales Between Current Month and Two Months Ahead:
SELECT SalesMonth, SalesAmount, LEAD(SalesAmount, 2) IGNORE NULLS OVER (ORDER BY SalesMonth) AS SalesTwoMonthsAhead FROM SalesData;Identify Consecutive Months with Sales Increase:
WITH CTE AS ( SELECT SalesMonth, SalesAmount, LAG(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS PreviousMonthSales FROM SalesData ) SELECT SalesMonth, SalesAmount FROM CTE WHERE SalesAmount > PreviousMonthSales;Find Months with No Sales and Their Preceding Sales Month:
SELECT SalesMonth, SalesAmount, LAG(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS PrecedingMonthSales FROM SalesData WHERE SalesAmount IS NULL;Calculate Cumulative Sales Sum Ignoring NULLs:
SELECT
SalesMonth,
SalesAmount,
SUM(ISNULL(SalesAmount, 0)) OVER (ORDER BY SalesMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSales
FROM
SalesData;
Identify the First Month with Sales After a Month with NULL Sales:
SELECT SalesMonth, SalesAmount, LEAD(SalesAmount, 1) IGNORE NULLS OVER (ORDER BY SalesMonth) AS FirstNonNullSalesAfterNull FROM SalesData WHERE SalesAmount IS NULL;Conclusion 🎉
The LAG and LEAD functions with the IGNORE NULLS option in SQL Server 2022 offer a more refined way to analyze data, providing more accurate and meaningful results. Whether you’re analyzing sales data, customer behavior, or any other time series data, these functions can significantly enhance your analytical capabilities.
Happy querying! 🚀
For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.