Get previous column vale till it reaches another not null value

Database
KVB
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
Teradata Employee

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.

SEL
M.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_CD
FROM td3471.MAIN M LEFT JOIN td3471.LKP L ON M.EMPNO=L.EMPNO AND M.EFF_DT=L.EFF_DT;

Please let me know if I had missed anything.

Khurram
KVB
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

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

Bikky,

You can get a detailed information about this clause at teradata info home. The URL is as below:

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/Orde...

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
KVB
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

Teradata Employee

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,

Vlad.

KVB
Enthusiast

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

Yes.I have approached using start and end dates concepts.But still,bcoz of this i came to know one new option in OLAP.Thank you.