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

• water490

  SSC Eights!

  Points: 963

  More actions

  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