SQL Server LEAD() Function

-

This blog is going to illustrate the functionality of SQL LEAD() and how to use it.

The SQL LEAD () function gives the next row of data for the offset value. This functionality was introduced in SQL Server 2012. Let’s see how to use it.

The following is the SampleTable.

SELECT SampleID, [Year], Profit
FROM SampleTable

The following query illustrates how to use the LEAD() function.

SELECT SampleID, Year, Profit,
LEAD(Profit, 1) OVER (ORDER BY SampleID ASC) AS NextYearProfit
FROM SampleTable

The following image is the output of the query above. Here you will find the column NextYearProfit. This shows the next profit.


Default Value

In the previous example, you can see that the last NextYearProfit column value is null. You can set the default value instead of zero if you want.

SELECT SampleID, [Year], Profit,
LEAD(Profit, 1, 7000000) OVER (ORDER BY SampleID ASC) AS NextYearProfit
FROM SampleTable

In the image below you can see that the last NextYearProfit value is ‘7000000’ instead of null

OFFSET value 2

Also, you can get the OFFSET value 2. That is next year’s next year value.

SELECT SampleID, [Year], Profit,
LEAD(Profit, 2) OVER (ORDER BY SampleID ASC) AS OffSet2
FROM SampleTable

In the picture, the profit of 2018 appears in 2016, the profit of 2019 appears in 2017, and so on.

PARTITION BY

You can use the LEAD() function using the PARTITION BY clause.

SELECT SampleID, [Quarter], [Year], Profit,
LEAD(Profit, 1) OVER (PARTITION BY [Year] ORDER BY [Year] ASC) AS NextYearProfit
FROM SampleTable 

In the image below you can see the result partition by the year.


I hope this article helps you. Keep Coding

Comments

Post a Comment

Popular posts from this blog

Entity Framework Core (EF) with SQL Server LocalDB

-
Image
This blog is going to illustrate how to implement Entity Framework (EF) Core with SQL Server LocalDB. The Entity Framework Core enables access to data from the database. This allows developers to avoid having to write most of the data access code. And the SQL Server LocalDB works similarly to the SQL SERVER with few features. You can get the LocalDB when you install visual studio or visual studio express. You can find the .mdf and _log.ldf files in the C:/Users/{user} directory. Let see how to create a SQL Server LocalDB and implement Entity Framework Core Step 1: The first step is to add a connection. In the menu -> click Tools and select Connect to Database It will display the Add Connection window. Enter (localdb)\mssqllocaldb in the server name textbox. Then in the Select or enter a database, select master and click ok Now in the server explorer, you can find the master DB containing Tables, Views, Stored
Read more

Exploring EventCallback in Blazor: Building Interactive Components

-
Image
Introduction: Blazor is a powerful framework for building web applications using C# instead of JavaScript. It allows developers to create interactive and dynamic user interfaces with ease. One of the key features in Blazor is the EventCallback<T> pattern, which enables communication between components through events. In this blog post, we will explore how to use the EventCallback<T> pattern in Blazor by building a simple example. Creating the Event-Driven Component: Let's start by creating a Blazor component called MyComponent. This component will raise an event when a button is clicked. Here's the code for the MyComponent.razor file: MyComponent.razor < h3 > My Component < / h3 > < button @onclick = " RaiseEvent " > Raise Event < / button > @code { [ Parameter ] public EventCallback < string > EventRaised { get ; set ; } private async Task RaiseEvent ( ) { await EventRais
Read more

A Step-by-Step Guide to Implementing Identity in ASP.NET Core MVC

-
Image
Are you ready to enhance your ASP.NET Core MVC project with the power of Identity? In this comprehensive guide, we'll walk you through the process of setting up and customizing the Identity system in your web application. By the end of this tutorial, you'll be able to implement secure user authentication and authorization for your ASP.NET Core project. Step 1: Create a New ASP.NET Core Web App Begin by opening Visual Studio and selecting "ASP.NET Core Web App (Model-View-Controller)" as your project template. Follow these steps: 1. Open Visual Studio and create a new project, selecting "ASP.NET Core Web App (Model-View-Controller)". 2. In the "Configure your project" window, enter the project name and choose the project's location. 3. In the "Additional Information" window, select the framework and choose "Individual Accounts" as the Authentication type. 4. Click "Create" to generate your project.
Read more