How to get Previous Row value in a table in SAP HANA/HANA SQL

Hello everyone,

REQUIREMENT

To access previous row value in a table. When we have null value in the current row then we have to look backward for the previous rows till we find a value .

In this blog post we will discuss how to get previous row value in a table and to explore further on how to access next row value please have a look at my BLOG POST (Scenario 1).

Before proceeding further I would like to thank @konrad for his suggestion to improvise this blog post.

Scenario 2: – To access previous row value

DATA

We will use the same table in this case also as used in Scenario 1, which has four columns namely PRODUCT, LOCATION, QTY (QUANTITY), DATE.

This table contains the list of ordered quantity of a product (i.e. material) from a location (i.e. plant) for an entire year. If you look at the table, In Jan, we have ordered 10 quantity and there is null quantity ordered in Feb. Similarly, we have values of ordered quantity for the rest of the months.

Now, when I say we have to look backward/previous months whenever there is a null value for QTY, then for Feb and March, we should have values from Jan (which is the first non-null value month before Feb and March). Similarly, for May, Aug, Sept and Nov we have to look previous months for value.

In simple words,

• Feb and March will have values from Jan.
• May will have value from April.
• Aug and Sept will have values from July.
• Nov will have value from Oct.

To achieve this also, I have used Table Function, which can be further consumed in a Calculation view to get the result.

SOLUTION

Scenario 2: – To access previous row value

DETERMINE FROM_DATE and TO_DATE RANGES FOR GIVEN NON-EMPTY QTY

As you can see the output, value from Jan should be replicated to Feb and Mar, so FROM_DATE is Jan and TO_DATE is Mar, similarly, you can see values of other months too.

SELECT "PRODUCT", 
       "LOCATION", 
       "QTY", 
       "DATE" AS "FROM_DATE",
       IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT", "LOCATION" ORDER BY  "DATE") , -1), "DATE") AS "TO_DATE"
       FROM "SCHEMA"."BASE_TABLE"
       WHERE "QTY" IS NOT NULL
       ORDER BY "DATE";

USE GENERATE_SERIES (or M_TIME_DIMENSION table) TO GENERATE TIME SERIES (monthly intervals)

Here, we will use generate series to generate a year to cover from Jan to Dec, to support a wider date range you can simply change the value of Dates passed in the function.

SELECT 
       "GENERATED_PERIOD_START" AS "DATE"
    FROM 
    	SERIES_GENERATE_DATE('INTERVAL 1 MONTH', '2021-01-01', '2022-01-01') TIME_SERIES;

FINAL Result

We can get the final result by Joining Time Series with the Ranges to get QTY for each date.

 SELECT
	 "PRODUCT",
	 "LOCATION",
	 "QTY" ,
	 "GENERATED_PERIOD_START" AS "DATE" 
         FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH','2010-01-01','2050-01-01') TIME_SERIES JOIN ( 
   SELECT
	 "PRODUCT",
	 "LOCATION",
	 "QTY",
	 "DATE" AS "FROM_DATE" ,
	 IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , -1), "DATE") AS "TO_DATE" 
	FROM "SCHEMA"."BASE_TABLE" 
	WHERE "QTY" IS NOT NULL 
	ORDER BY "DATE" ) TAB ON TIME_SERIES."GENERATED_PERIOD_START" BETWEEN TAB."FROM_DATE" AND TAB."TO_DATE" 
;

CODE: –

FUNCTION "XXXX"."YYYYY::TF_LOOKING_BACKWARD" ( ) 
	
      RETURNS TABLE (
	               "PRODUCT" NVARCHAR(18),
	               "LOCATION" NVARCHAR(4),	               
	               "QTY" DECIMAL(15,6), 	               
	               "DATE" NVARCHAR(10)
	               )
	
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN

/***************************** 
Write your function logic
 *****************************/

     
var_out =  SELECT
	 "PRODUCT",
	 "LOCATION",
	 "QTY" ,
	 "GENERATED_PERIOD_START" AS "DATE" 
FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH','2010-01-01','2050-01-01') TIME_SERIES JOIN ( 
   SELECT
		 "PRODUCT",
		 "LOCATION",
		 "QTY",
		 "DATE" AS "FROM_DATE" ,
		 IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , -1), "DATE") AS "TO_DATE" 
		FROM "SCHEMA"."BASE_TABLE" 
		WHERE "QTY" IS NOT NULL 
		ORDER BY "DATE" ) TAB ON TIME_SERIES."GENERATED_PERIOD_START" BETWEEN TAB."FROM_DATE" AND TAB."TO_DATE" 
;	
return :var_out;

END;