Getting Null with Lag function

Transact-SQL
1

Can you take a look and let me know what I am doing wrong? My lag value is Null and it should be zero based how it's listed in the table. Here is my code - see link for output of tables and my results . I want to say starting from calendar key of 20160925 tell me what the previous value for Random_3 was. Am I using an incorrect function.
SELECT T1.ACCOUNT_NBR,
T1.CUSTOMER_KEY,
T2.RANDOM_1,
T2.RANDOM_2,
T2.RANDOM_3,
LAG(T2.RANDOM_3, 1) OVER (ORDER BY T2.CALENDAR_KEY)AS LAGVALUE
FROM T1
JOIN T2
ON T1.CUSTOMER_KEY = T2.CUSTOMER_KEY
AND T1.ACCOUNT_NBR = T2.ACCOUNT_NBR
AND T1.WO_CHECKED_IN_DT_KEY = T2.CALENDAR_KEY

Table look and Output.jpg979×459 134 KB

2

You have a field, WO_CHECKED_IN_DT_KEY , for which you are not displaying the value , in excel/picture.

From BOL -LAG : LAG (Transact-SQL) - SQL Server | Microsoft Learn

If a default value is not specified, NULL is returned.

One way to solve it , is to change your LAG function to this:

LAG(T2.RANDOM_3, 1 , 0) OVER (ORDER BY T2.CALENDAR_KEY) AS LAGVALUE

Why is displaying NULL ? because you apply LAG after you are doing the join. To better understand, comment the line with LAG and see that you are getting only one record.
Then apply LAG to your data set ( which is one record) - no record is available to match this function (there is no previous record) - so it returns NULL.



Another way to do it:

declare @T1 TABLE
( Account_NBR BIGINT,
  Customer_Key BIGINT
  ,WO_CHECKED_IN_DT_KEY BIGINT
);
INSERT INTO @T1(Account_NBR,Customer_Key,WO_CHECKED_IN_DT_KEY)
VALUES (38568806,103322035,20160925);

declare @T2 TABLE
(Calendar_key BIGINT,
 Account_NBR BIGINT,
 Customer_Key BIGINT,
 Random_1 TINYINT,
 Random_2 TINYINT,
 Random_3 TINYINT);

INSERT INTO @T2(Calendar_key,Account_NBR,Customer_Key,Random_1,Random_2,Random_3)
VALUES(20160903,38568806,103322035,1,1,0),
      (20160922,38568806,103322035,1,1,0),
      (20160925,38568806,103322035,1,1,1),
      (20161006,38568806,103322035,1,1,1),
      (20161101,38568806,103322035,1,1,1);

SELECT 
    T1.ACCOUNT_NBR,
    T1.CUSTOMER_KEY,
    T2.RANDOM_1,
    T2.RANDOM_2,
    T2.RANDOM_3
    --,LAG(T2.RANDOM_3, 1,0) OVER (ORDER BY T2.CALENDAR_KEY)AS LAGVALUE
    ,T2.LAGVALUE
FROM 
    @T1 AS T1
    INNER JOIN 
    (SELECT 
        Calendar_key,Account_NBR,Customer_Key,Random_1,Random_2,Random_3
        ,LAG(T2.RANDOM_3, 1) OVER (PARTITION BY Account_NBR,Customer_Key ORDER BY T2.CALENDAR_KEY) AS LAGVALUE
     FROM
        @T2 AS T2
    ) AS T2
     
    ON T1.CUSTOMER_KEY = T2.CUSTOMER_KEY
    AND T1.ACCOUNT_NBR = T2.ACCOUNT_NBR
    AND T1.WO_CHECKED_IN_DT_KEY = T2.CALENDAR_KEY

To output for this:

ACCOUNT_NBR          CUSTOMER_KEY         RANDOM_1 RANDOM_2 RANDOM_3 LAGVALUE
38568806             103322035            1        1        1        0