My source table Table1 contains columns WELLID,ORIG_REF, LOG_DATE
Sample data:
Code: Select all
WELL_ID ORIG_REF LOG_DATE
------- -------- --------
1 CALIB 11/19/2000
2 BRITAN 6/30/2006
3 CALGRY NULL
4 DENVAR 8/20/2009
5 ALBERTA NULL
columns:[ORIG_REF,END_DATE,ENT_NAME] based on key column Table1.ORIG_REF
from this table i have to fetch ENT_NAME based on WELL_ID,END_DATE
reference table Sample data:
Code: Select all
WELL_ID ORIG_REF END_DATE ENT_NAME
------- -------- -------- --------
1 CALIB 11/19/2001 X
1 CALIB 6/30/2006 Y
1 CALIB 10/19/2000 Z
1 CALIB NULL AB
2 BRITAN 6/30/2006 P
2 BRITAN 6/30/2004 Q
2 BRITAN NULL R
3 CALGRY 6/30/2006 C
3 CALGRY 6/30/2005 D
3 CALGRY NULL F
5 ALBERTA 6/30/2005 QA
5 ALBERTA 8/30/2005 CBZ
condition #1:
for a given orig_ref:CALIB in reference table contains 4 records, from this i have to fetch ENT_NAME based on LOG_DATE and END_DATE.
i have to fetch ENT_NAME in such a way that LOG_DATE [ON or BEFORE] END_DATE.
i.e., log_date value : 11/19/2000, END_DATE values are [11/19/2001,6/30/2006,10/19/2000,NULL] here log_date on or before thatdate value i have to take from reference table,
value is 10/19/2000 hence for this record ENT_NAME: z this value i have to fetch.
condition #2:
for a given orig_ref:BRITAN in reference table contains 3 records, source log_date : 6/30/2006 exactly matches with END_DATE: 6/30/2006 hence
for this i have to fetch ENT_NAME: P
condition #3:
for a given orig_ref:CALGRY in reference table contains 3 records, source log_date : NULL, Reference table END_DATE also contains NULL value hence
for this i have to fetch ENT_NAME: F
note : if the reference table does'nt have value with NULL then hardcode ENT_NAME:'NOT APPLICABLE'
condition #4:
for a given orig_ref:ALBERTA in reference table contains 2 records,source log_date : NULL,BUT Reference table END_DATE contains date:6/30/2005,8/30/2005
here there is no NULL date in reference table hence hardcode ENT_NAME:'NOT APPLICABLE'
condition #5:
for a given orig_ref:DENVAR in reference table does't have any records for this hardcode ENT_NAME:'NOT APPLICABLE'
output :
Code: Select all
WELL_ID ORIG_REF LOG_DATE ENT_NAME
------- -------- -------- --------
1 CALIB 11/19/2000 Z
2 BRITAN 6/30/2006 P
3 CALGRY NULL F
4 DENVAR 8/20/2009 NOT APPLICABLE
5 ALBERTA NULL NOT APPLICABLE