Transact SQL – Windowing Functions – Lag and Lead

Background

Have used Windowing Functions for years now.

But, never used Lag and Lead.

Just thought it was too hard to layer on top of what was already hard for me.

 

Easy

Finally today, I ended up dealing with something actually hard for me.

A good man gracefully scaled it and he sprinkled in lag and lead.

Added Bonus cause the implementation was cute as …

 

Code

Outline

  1. Create Table
    • DDL
      • Using a simple Grade Table
      • Columns
        • Percentile
        • Grade
  2. Added data
    • DML
      • Add 60 => D
      • Add 70 => C
      • Add 80 => B
      • Add 90 => A
  3. Query
    • Display Data
      • Display current record
      • Fetch value from previous record
        • Use Lag
          • Column:- percentile
          • Step:- 1 Back
          • Default Value ( if no previous record) :- 0
      • Fetch value from next record
        • Use Lead
          • Column:- percentile
          • Step:- 1 Forward
          • Default Value ( if no next record) :- 100

 

SQL

 


set nocount on
go

set XACT_ABORT on
go

declare @tblGrade TABLE
(

      [id]         tinyint identity(1,1)
    , [percentile] int not null
    , [grade]      char(1) not null
)

insert into @tblGrade
([percentile], [grade])
values
  (60, 'D')
, (70, 'C')
, (80, 'B')
, (90, 'A')


SELECT 
            tblG.*

        ,  [percentilePrevious]
              = Lag
              (
                  tblG.percentile -- column
                , 1   -- step
                , 0   -- default
              ) 
              OVER
              (
                ORDER 
                    BY tblG.percentile ASC
              )  

        , [percentileNext]
            = Lead
              (
                  tblG.percentile -- column
                , 1   -- step
                , 100 -- default
              ) 
              OVER
              (
                ORDER 
                    BY tblG.percentile ASC
              )  

        , [range]
            = cast(tblG.percentile as varchar(10))
                + ' - '
                +   cast
                    (
                            Lead
                            (
                                  tblG.percentile -- column
                                , 1   -- step
                                , 100 -- default
                            ) 
                            OVER
                            (
                                ORDER BY 
                                    tblG.percentile ASC
                            ) 
                        as varchar(10)
                    )

FROM @tblGrade tblG

order by
        tblG.[percentile] asc


 

Output

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s