Post reply

how to generalize the LAG function so it keeps looking until a value is found?

  • December 16, 2024 at 3:36 am

    #4498834

    Hi everyone

    There are situations where a value for a particular day is NULL so I use the previous day's value hoping it isn't NULL.  I use LAG for this.  The problem is that sometimes even the previous value is NULL so I have to use LAG(2) to get the 2nd previous value.  This approach only works if I know in advance how many previous records I need to search for.  This isn't practical.  Is there a way to keep looking at prior records until the first non-NULL value is located?

    Test Date:

    DROP TABLE IF EXISTS #TestTable
    DROP TABLE IF EXISTS #TEMP1

    CREATE TABLE #TestTable
    (
    [UNIT] [nvarchar](10) NOT NULL,
    [PURCHASE_DATE] [date] NOT NULL,
    [METRIC1] [float] NULL,
    [METRIC2] [float] NULL
    )

    INSERT INTO #TestTable
    VALUES ('ABC','2024-12-12',435.090,210.33),
           ('ABC','2024-12-09',213.390,4013.4901),
       ('DEF','2024-12-12',34,99),
       ('ABC','2024-12-10',NULL,.008),
       ('DEF','2024-12-11',57.903,9),
       ('DEF','2024-12-10',440.023,62),
       ('ABC','2024-12-08',33.924,80.02),
       ('DEF','2024-12-08',NULL,12),
       ('DEF','2024-12-07',NULL,48.90),
       ('DEF','2024-12-06',21.984,16.33)

    Expected Outcome:

    For DEF 2024-12-08 it should use 21.984 for Metric1 because that is the first non-NULL value prior to 2024-12-08

    For DEF 2024-12-07 it should use 21.984 for Metric1 because that is the first non-NULL value prior to 2024-12-07

    For ABC 2024-12-10 it should use 213.39 for Metric1 because that is the first non-NULL value prior to 2024-12-10

    Thank you

  • December 16, 2024 at 3:31 pm

    #4499116

    IF you're on SQL 2022, you can add "IGNORE NULLS" to the LAG function.

    Otherwise you can use an OUTER APPLY with a SELECT TOP (1).  For example:

    SELECT tt1.*, oa1.METRIC1 AS METRIC1_PREVIOUS
    FROM #TestTable tt1
    OUTER APPLY (
        SELECT TOP (1) *
        FROM #TestTable tt2
        WHERE tt2.UNIT = tt1.UNIT AND tt2.PURCHASE_DATE < tt1.PURCHASE_DATE AND tt2.METRIC1 IS NOT NULL
        ORDER BY tt2.PURCHASE_DATE DESC
    ) AS oa1

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 16, 2024 at 4:52 pm

    #4499152

    Or combine with the unique ordering column:

    SELECT UNIT, PURCHASE_DATE, METRIC1
    ,CAST( SUBSTRING( MAX (
    CONVERT(char(8),  PURCHASE_DATE, 112) + CAST(Metric1 AS varchar(20))
    )
    OVER (
    PARTITION BY Unit
    ORDER BY PURCHASE_DATE
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ), 9, 255) AS DECIMAL(19,4)) AS LagMetric1
    ,METRIC2
    FROM #TestTable
    ORDER BY UNIT, PURCHASE_DATE;

  • December 17, 2024 at 4:58 am

    #4499365

    Thank you both!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply