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

SignalR with JavaScript in ASP.NET Core MVC

-
Image
This blog is going to explain what is SignalR and how to implement it in ASP.NET Core. SignalR is a real time application framework for ASP.NET Core. Using SignalR you can provide updated information without refreshing the page, for example poll results, game score, chat etc. Let's see how to achieve it in ASP.NET Core MVC. Step 1: First step I added the SignalR client library. To add a SignalR client library, Right click on the project in Solution Explorer and Add -> Client-Side Library. Select the provider as unpkg as shown in the image below. Enter the latest version of SignalR into the library. And selected signalr.js and signalr.min.js files. Finally clicked the install button to install the client library. Step 2: At this step I have created a VoteHub.cs class inside the Hubs folder(create a new folder called Hubs). This class inherits from the Hub class which is a base class of SignalR hub. The SendMessage method will be called by the connected client.
Read more

Localization in ASP.NET Core MVC with Example

-
Image
Using Query Strings for Request Culture and Simplifying Localization with IStringLocalizer Interface Let’s see how to implement Localization in ASP.NET Core MVC. In the first example, I am going to use the IStringLocalizer Interface in the controller to implement Localization. Additionally, when the user passes the desired language through the query string, it will display content in that language. First, let’s configure the settings in the program.cs file. builder.Services.AddLocalization(options => options.ResourcesPath = "Resources");  The AddLocalization method adds the localization service to the service container. I have added the supported cultures here. I have added two cultures: en for English and nl for Dutch. In the RequestCultureProviders , I have included QueryStringRequestCultureProvider() . This allows the system to determine the culture information from the query string. UseRequestLocalization initializes a RequestLocalizationOptions objec
Read more