SQL Server LAG() Function

-

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

The SQL LAG() function gives the previous row data of the offset value. This functionality has been introduced on the SQL Server 2012.

The following is the sample table. There are three columns in table SampleID, Year, and Profit. Let’s see how to get the previous row value. 

SELECT * FROM SampleTable

The following is an example of the SQL query LAG() function. The query explains how to get the previous year’s profit.

SELECT SampleID, Year, Profit, 
LAG(Profit, 1) OVER (ORDER BY SampleID ASC) AS PreviousProfit 
FROM SampleTable

The following table shows the output of the query above. You can see the new column called PreviousProfit here. The first row of the PreviousProfit column is null because there is no previous value.


Default value

When using the LAG() function, the first result will be null. But it can be assigned a default value. In the following query, the default value is added as 100000

SELECT SampleID, Year, Profit, 
LAG(Profit, 1, 100000) OVER (ORDER BY SampleID ASC) AS PreviousProfit 
FROM SampleTable

The following image is the output of the default value for the LAG() function, you can see that the first-row value of the PreviousProfit column is 100000


OFFSET value 2

Also, OFFSET value 2 can be used to get the first-row value in the third row instead of the second row.

SELECT SampleID, Year, Profit, 
LAG(Profit, 2, 100000) OVER (ORDER BY SampleID ASC) AS PreviousProfit 
FROM SampleTable


PARTITION BY

You can use the LAG() function with the PARTITION BY clause.

SELECT SampleID, [Quarter], [Year], Profit, 
LAG(Profit, 1, 100000) OVER (PARTITION BY [Year] ORDER BY [Year] ASC) AS PreviousProfit 
FROM SampleTable

In the following figure, you can see how the LAG() function returns results using the PARTITION BY clause. LAG() returns the result based on the partition. Each partition is assigned a default value (100000).


I hope this will help 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