SQL SERVER lead and lag use

Example: Get records that are duplicated within 48 hours

SELECT  * from(SELECTB.*, LAG (B.operatortime,1, B.operatortime) Over(PARTITION byB.NoORDER  byB.operatortime) asBefortime, Lead (B.operatortime,1, B.operatortime) Over(PARTITION byB.NoORDER  byB.operatortime) asNexttime fromTest b) aWHERE   DATEDIFF(HH, A.befortime, A.operatortime)<  -         and DATEDIFF(HH, A.operatortime, A.nexttime)<  -         andA.noinch(SELECTc.no fromdbo. Test CGROUP  byc.no having   COUNT(c.no)> 1)

Lag function:

Function: Accesses data that precedes the same result set, without using a self-join in SQL Server 2016. LAG provides access to rows at a given physical offset before the current line. Use this analysis function in a SELECT statement to compare the values in the current row with the values in the previous row.

Grammar:

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

Parameters:

Scalar_expression
The value to return based on the specified offset. This is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

Offset amount
The number of rows after the current row (from which to get the value). If not specified, the default value is 1. offsets can be columns, subqueries, or other expressions that evaluate to a positive integer, or can be implicitly converted to bigint. The offset cannot be a negative value or an analytic function.

Default value
The value to return when the scalar_expression is offset to NULL. If no default value is specified, NULL is returned. The default can be a column, subquery, or other expression, but not a function that can be parsed. The default must be type compatible with scalar_expression.

by ( [ partition_by_clause ] order_by_clause)
Partition_by_clause is divided into the result set generated by the FROM clause to which the partition function is applied. If not specified, this function treats all rows of the query result set as a single group. Order_by_clause determines the order of the data before applying the function. If partition_by_clause is specified, it determines the order of the data in the partition. Order_by_clause is required.

Select-over clause:

Function: Determines the partitioning and sorting of rowsets before applying the associated window function. That is, the over clause defines a window in a query result set or a user-specified rowset. The window function then calculates the value of each row in the Windows. You can use the over clause with a function to calculate various aggregated values, such as moving averages, cumulative aggregations, running totals, or the first N results of each group of results.

Example:

SELECTSalesOrderID, ProductID, OrderQty,SUM(OrderQty) Over(PARTITION bySalesOrderID) asTotal ,AVG(OrderQty) Over(PARTITION bySalesOrderID) as"AVG"      ,COUNT(OrderQty) Over(PARTITION bySalesOrderID) as"Count"      ,MIN(OrderQty) Over(PARTITION bySalesOrderID) as"Min"      ,MAX(OrderQty) Over(PARTITION bySalesOrderID) as"Max"   fromSales.SalesOrderDetailWHERESalesOrderIDinch(43659,43664); SalesOrderID ProductID OrderQty TotalAVG         Count       Min    Max  ------------ ----------- -------- ----------- ----------- ----------- ------ ------  43659        776         1         -          2            A          1      6  43659        777         3         -          2            A          1      6  43659        778         1         -          2            A          1      6  43659        771         1         -          2            A          1      6  43659        772         1         -          2            A          1      6  43659        773         2         -          2            A          1      6  43659        774         1         -          2            A          1      6  43659        714         3         -          2            A          1      6  43659        716         1         -          2            A          1      6  43659        709         6         -          2            A          1      6  43659        712         2         -          2            A          1      6  43659        711         4         -          2            A          1      6  43664        772         1         -          1           8           1      4  43664        775         4         -          1           8           1      4  43664        714         1         -          1           8           1      4  43664        716         1         -          1           8           1      4  43664        777         2         -          1           8           1      4  43664        771         3         -          1           8           1      4  43664        773         1         -          1           8           1      4  43664        778         1         -          1           8           1      4  

Lead:

Accesses data in subsequent rows of the same result set, instead of using a self-join in SQL Server 2012. The lead provides access to the row at a given physical offset after the current line. Use this analysis function in a SELECT statement to compare the values in the current row with the values in subsequent rows.

Syntax: Lead (scalar_expression [, offset], [default]) over ([Partition_by_clause] order_by_clause)

Scalar_expression, the value to return is based on the specified offset. This is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function

The default value of offset is 1, offset can be a column, subquery, or other expression that evaluates to a positive integer, or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

Default defaults to NULL, offset can be a column, subquery, or other expression that evaluates to a positive integer, or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

Lead and lag are very similar, except that lag is a forward evaluation

SQL SERVER lead and lag use