All Forums Database
KVB 124 posts Joined 09/12
15 Nov 2013
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
 
 
 

VBurmist 96 posts Joined 12/09
15 Nov 2013

 
 
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

M.Saeed Khurram 544 posts Joined 09/12
15 Nov 2013

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 124 posts Joined 09/12
15 Nov 2013

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

M.Saeed Khurram 544 posts Joined 09/12
15 Nov 2013

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/Ordered_Analytical_Functions.083.010.html
 

Khurram

M.Saeed Khurram 544 posts Joined 09/12
15 Nov 2013

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 124 posts Joined 09/12
15 Nov 2013

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

M.Saeed Khurram 544 posts Joined 09/12
VBurmist 96 posts Joined 12/09
16 Nov 2013

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 124 posts Joined 09/12
20 Nov 2013

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.

bhartiya007 29 posts Joined 03/14
22 Sep 2015

just use your year in the join column:
 
SEL M.EMPNO AS MAIN_EMP, M.EFF_DT AS MAIN_DT, L.TYPE_CD FROM MAIN M LEFT OUTER JOIN LKP L
ON M.EMPNO=L.EMPNO AND EXTRACT(YEAR FROM M.EFF_DT)=EXTRACT(YEAR FROM L.EFF_DT)

@Amit

shivkumar 6 posts Joined 07/13
23 Sep 2015

I solved my issue using your solution...
 
Thank you so much saeed

You must sign in to leave a comment.