Get the data based by combining tables and then by date.

[Raj158]

Hi,

Table 1:  

Data_TEMP:
load
Countrycode, Branch_Code,
Unit_Id as UNT_NUMBER,
date(Createddate,'YYYY-MM-DD') as IOT_CREATEDDATE
Resident ALL_DATA_LOAD_MAP

Table 2:

IOT_TEMP:
LOAD
COUNTRYCODE as Countrycode , BUCODE as Branch_Code,
EXISTINGFHR,date(EFFECTIVEDATE,'YYYY-MM-DD') as EFFECTIVEDATE,
if (isnull(EFFECTIVEENDDATE),date(today(),'YYYY-MM-DD'), date(EFFECTIVEENDDATE,'YYYY-MM-DD')) as EFFECTIVEENDDATE,
NEWFHR
from $(vStorePath)IOT_RAE.qvd(qvd);

I would like to join these two tables and create table like following
Countrycode, Branch_Code, UNT_NUMBER, IOT_CREATEDDATE , EFFECTIVEDATE, EFFECTIVEENDDATE, FHR

NOTE: FHR is calculated field where value should be between of EFFECTIVEDATE and EFFECTIVEENDDATE against each line.

[jochem_zw]

This should do it: