Lead and Lag Functions in SQL Server

SQL Server 2012 introduced new analytical function LEAD() and LAG().

These functions accesses data from nth next row and nth previous row in the same result set without the use of a self-join

• LEAD():  Used to access data from nth next row in the same result set without the use of a self-join.
• LAG(): Used to access data from nth previous row in the same result set without the use of a self-join

Syntax

LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Parameters

• scalar_expression: column name for which value to be accessed
• offset: nth previous (for lag) or next (for lead) row  to access of the column
• default: default value to display if nth row not exists

Example - 1

DECLARE @Test_table TABLE(
    Year INT, Sale INT
)

INSERT INTO @Test_table VALUES
('2008',5000000), ('2009',5500000), ('2010',5250000), ('2011',6025000), ('2012',6200000)

SELECT    Year, Sale
        , LEAD(Sale) OVER (ORDER BY Year) AS [Next Year Sale]
        , LAG(Sale) OVER (ORDER BY Year) AS [Prev Year Sale]
        , LEAD(Sale, 2) OVER (ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
        , LEAD(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
FROM    @Test_table

RESULT

Example - 2 (With Partition By)

DECLARE @Test_table TABLE(
    Year INT, Zone VARCHAR(10),  Sale INT
)

INSERT INTO @Test_table VALUES
 ('2009', 'East', 5500000), ('2010', 'East', 5250000), ('2011', 'East', 6025000), ('2012', 'East', 6200000)
,('2009', 'West', 5200000), ('2010', 'West', 5250000), ('2011', 'West', 5525000), ('2012', 'West', 5700000)
,('2009', 'North', 4700000), ('2010', 'North', 4800000),('2011', 'North', 5000000), ('2012', 'North', 5050000)
,('2009', 'South', 7200000), ('2010', 'South', 7500000), ('2011', 'South', 7800000), ('2012', 'South', 8000000)

SELECT    Year, Sale
        , LEAD(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Next Year Sale]
        , LAG(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Prev Year Sale]
        , LEAD(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
        , LEAD(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
FROM    @Test_table

RESULT

Auto Generate Employee Id in SQL Server

In this post, I have explained how Employee Id can be auto generated very simply with an identity and a computed column.

Suppose, we have to generate employee id's in the format AA00001 where

• 1st character - 1st character of First Name
• 2nd character - 2st character of Last Name
• Next 5 characters - Sequential Numerical value

To generate sequential values take an identity column that would do the auto increment for you.
Now take a Computed Column to generate employee id and set the formula to generate employee id in the desired format like below.

Now you may have the requirement to make the employee id column as Primary Key. For this, make the Computed Column as Persisted, as primary key can be created on Computed Column only if the column is Persisted.

CREATE TABLE tblEmployee(
    Id INT identity
    , EmpId AS (LEFT(EmpFirtsName,1) + LEFT(EmpLastName,1)
      + REPLICATE('0', 5-LEN(Id)) + CAST(Id AS VARCHAR)) PERSISTED PRIMARY KEY
    , EmpFirtsName VARCHAR(50) NOT NULL
    , EmpLastName VARCHAR(50) NOT NULL
)

INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Ajay', 'Anand')
INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Sanjay', 'Singh')
INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Vijay', 'Kumar')

SELECT * FROM tblEmployee

RESULT

Merge two DataTables in C#

In this post, I am sharing how Merge() method is used to merge two DataTables in C# / ASP.Net

There are certain points need to be considered when using Merge() method to merge datatables.

Let's assume there are 2 datatables and we have to merge both the datatables on the basis of Employee Id then,

• Both the DataTable should have a column with the same name say "EmpId".
• Values in columns "EmpId" of both the tables should be unique.

MergeTable.aspx Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MergeTable.aspx.cs" Inherits="MergeTable" %>
<html>
<head runat="server">
    <title>Merge Two DataTables</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>

MergeTable.aspx.cs

using System;
using System.Data;

public partial class MergeTable : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dataTable1 = null, dataTable2 = null, dataTable3 = null;
            dataTable1 = DataTable1();
            dataTable2 = DataTable2();
            dataTable1.PrimaryKey = new DataColumn[] { dataTable1.Columns["EmpId"] };
            dataTable2.PrimaryKey = new DataColumn[] { dataTable2.Columns["EmpId"] };
            dataTable3 = dataTable1.Copy();
            dataTable3.Merge(dataTable2, false, MissingSchemaAction.Add);
            dataTable3.AcceptChanges();
            GridView1.DataSource = dataTable3;
            GridView1.DataBind();
        }
    }

    private DataTable DataTable1()
    {
        DataRow dataRow = null;
        DataTable dt1 = new DataTable();
        dt1.Columns.Add("EmpId");
        dt1.Columns.Add("EmpName");
        dataRow = dt1.NewRow();
        dataRow["EmpId"] = "EMP001";
        dataRow["EmpName"] = "Ajaj Kumar";
        dt1.Rows.Add(dataRow);
        dataRow = dt1.NewRow();
        dataRow["EmpId"] = "EMP002";
        dataRow["EmpName"] = "Sanjay Gupta";
        dt1.Rows.Add(dataRow);
        dt1.AcceptChanges();
        return dt1;
    }

    private DataTable DataTable2()
    {
        DataRow dr = null;
        DataTable dt2 = new DataTable();
        dt2.Columns.Add("EmpId");
        dt2.Columns.Add("Salary");
        dr = dt2.NewRow();
        dr["EmpId"] = "EMP001";
        dr["Salary"] = "50000";
        dt2.Rows.Add(dr);
        dr = dt2.NewRow();
        dr["EmpId"] = "EMP002";
        dr["Salary"] = "45000";
        dt2.Rows.Add(dr);
        dt2.AcceptChanges();
        return dt2;
    }
}