Dynamic LAG function

Question

I'm trying to get a valua based on a dynamic lag function, which I won't get. I have a table that looks like this, and what I want is the value from product from the Serial Number that is 1 higher then my current Serial Number

+--------+---------+--------+------------------------+----------+
| Client | Product | Ser_No | Product Ser_No+1 I get | Expected |
+--------+---------+--------+------------------------+----------+
|    1   |    A    |   1    |              B         |     B    |
+--------+---------+--------+------------------------+----------+
|    1   |    B    |   2    |              C         |     C    |
+--------+---------+--------+------------------------+----------+
|    1   |    C    |   3    |              D         |     F    |
+--------+---------+--------+------------------------+----------+
|    1   |    D    |   3    |              E         |     F    |
+--------+---------+--------+------------------------+----------+
|    1   |    E    |   3    |              F         |     F    |
+--------+---------+--------+------------------------+----------+
|    1   |    F    |   4    |              null      |    null  |
+--------+---------+--------+------------------------+----------+
|    2   |    D    |   1    |              E         |     E    |
+--------+---------+--------+------------------------+----------+
|    2   |    E    |   2    |              F         |     G    |
+--------+---------+--------+------------------------+----------+
|    2   |    F    |   2    |              G         |     G    |
+--------+---------+--------+------------------------+----------+
|    2   |    G    |   3    |              H         |     H    |
+--------+---------+--------+------------------------+----------+ 
|    2   |    H    |   4    |              I         |     I    |
+--------+---------+--------+------------------------+----------+
|    2   |    I    |   5    |              null      |     null |
+--------+---------+--------+------------------------+----------+

The only thing I can come up with is the Lag(product, 1), but that is not exactly what I want right now. Any ideas?

This is what I do right now:

LEAD(Product, 1) OVER(PARTITION BY Cliënt ORDER BY Ser_No ASC)

But the problem is rows 3, 4 and 8. This is where i need the productcode not from the next row but from the next Ser_No

Your over() part of the clause is suspiciously missing from your question. It should be lag(product,1) over (partition by product order by ser_no asc). Is that what you have done? — George Menoutis

Zohar Peled Zohar Peled · Accepted Answer · 2019-12-12T11:39:46

I would probably use lead and not lag here:

SELECT  Client,
        Product,
        Ser_No,
        LEAD(Product) OVER(PARTITION BY Client ORDER BY Ser_No) As [Product Ser_No+1]
FROM TableName

To use Lag you would need order by Ser_No desc.

With this new edit, I would do it like this:

WITH CTE AS
(
    SELECT  Client,
            Product,
            Ser_No,
            DENSE_RANK() OVER(PARTITION BY Client ORDER BY Ser_No) As rnk
    FROM TableName
)


SELECT  Client,
        Product,
        Ser_No,
        (
            SELECT Product 
            FROM CTE t1 
            WHERE t1.Client = t0.Client 
            AND t1.Rnk = t0.Rnk + 1
        ) As [Product Ser_No+1]
FROM CTE t0

Dynamic LAG function

1 Answers