Database
Enthusiast

## Get previous column vale till it reaches another not null value

CT LKP(EMPNO INTEGER,TYPE_CD VARCHAR(10),EFF_DT DATE);

INS INTO LKP VALUES(1,'A','2013-01-01');

INS INTO LKP VALUES(1,'B','2014-01-01');

CT MAIN(EMPNO INTEGER,EFF_DT DATE);

INS INTO MAIN VALUES(1,'2012-01-01');

INS INTO MAIN VALUES(1,'2013-01-01');

INS INTO MAIN VALUES(1,'2013-03-01');

INS INTO MAIN VALUES(1,'2013-05-01');

INS INTO MAIN VALUES(1,'2013-07-01');

INS INTO MAIN VALUES(1,'2013-09-01');

INS INTO MAIN VALUES(1,'2014-01-01');

INS INTO MAIN VALUES(1,'2014-02-01');

INS INTO MAIN VALUES(1,'2014-03-01');

SEL

M.EMPNO AS MAIN_EMP,

M.EFF_DT AS MAIN_DT,

L.TYPE_CD

FROM MAIN M LEFT JOIN LKP L ON M.EMPNO=L.EMPNO AND M.EFF_DT=L.EFF_DT

Expected O/P

MAIN_EMP MAIN_DT TYPE_CD

1 1 2012-01-01 NULL

2 1 2013-01-01 A

3 1 2013-03-01 A

4 1 2013-05-01 A

5 1 2013-07-01 A

6 1 2013-09-01 A

7 1 2014-01-01 B

8 1 2014-02-01 B

9 1 2014-03-01 B

11 REPLIES

## Re: Get previous column vale till it reaches another not null value

select m.empno, m.eff_dt, p.type_cd

from

main as m

LEFT OUTER JOIN

(

select empno,

eff_dt as start_dt,

coalesce (max(eff_dt) over (partition by empno order by eff_dt rows between 1 following and 1 following) - 1

, date '9999-12-31') as end_dt,

type_cd

from lkp

)  as p

ON m.empno = p.empno

and m.eff_dt between p.start_dt and p.end_dt

order by 1,2

Enthusiast

## Re: Get previous column vale till it reaches another not null value

Hi Bikky,

I hope the following query will fullfil your requirement.

`SELM.EMPNO AS MAIN_EMP,M.EFF_DT AS MAIN_DT,--L.TYPE_CD,MAX(TYPE_CD) OVER(ORDER BY M.EFF_DT RESET WHEN TYPE_CD IS NOT NULL) AS NEW_TYPE_CDFROM td3471.MAIN M LEFT JOIN td3471.LKP L ON M.EMPNO=L.EMPNO AND M.EFF_DT=L.EFF_DT;`

Khurram
Enthusiast

## Re: Get previous column vale till it reaches another not null value

Excellent Saeed! I have never come accrsoo through RESET usage.Really it's an eye-opener.Could you please give me any link that provides more infor RESET usage

Enthusiast

Bikky,

Khurram
Enthusiast

## Re: Get previous column vale till it reaches another not null value

Bikky,

I dont know why my previous post is published empty. But you can get detailed information about this claues at teradata info home.

The URL is as follows:

`http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/Ordered_Analytical_Functions.083.010.html`
Khurram
Enthusiast

## Re: Get previous column vale till it reaches another not null value

Can you pls repost the link as I am unable to get it..I am seeing a scroll bar only.

Enthusiast

## Re: Get previous column vale till it reaches another not null value

Hi Bikky,

There is one more option.   Apart from using the Ordered Analytical Functions, you might want to introduce the Start_date and End_date into the LKP table.   This approach would correspond to the design when you have a Fact table, a Lookup table, and the condition for dates is:

Fact_table.Event_date between Lookup_table.Start_date and Lookup_table.End_date

This is useful when these tables are used by many people, not all of them being comfortable with Ordered Analytical Functions (they are really great though).

Regards,