Period Between

[upaliwije]

Hi

I have following data in one table

Table 1

Policy No

Premium

Product

From_date

To_date

In table 2 I have following data

Table 2

Policy No

Claim No

Loss date

Claim Paid

I want to load full data from table 1 and concatenate  data from  table 2  if Loss Date is between From_date and To_Date of table1

Pls help me to write the script for this with this condition

[anbu1984]

Table:

Load Policy_No,Premium,Product,From_date,To_date From Table1;

Join(Table1)

Load Policy_No,Claim_No,Loss_date,Claim_Paid From Table2;

NoConcatenate

Final:

Load * Resident Table where Loss_date >= From_date And Loss_date <= To_date;

[upaliwije]

Thanks

I think Join will not give the desired result as as in table one same policy no exists more than one time

[anbu1984]

Check this example

Table:

Load Policy_No,Premium,Product,Date#(From_date,'M/D/YYYY') As From_date,Date#(To_date,'M/D/YYYY') As To_date Inline [

Policy_No,Premium,Product,From_date,To_date

1,1,a,1/1/2014,2/1/2014

1,1,a,2/2/2014,3/1/2014

];

Join(Table)

Load Policy_No,Claim_No,Date#(Loss_date,'M/D/YYYY') As Loss_date,Claim_Paid Inline [

Policy_No,Claim_No,Loss_date,Claim_Paid

1,1,1/2/2014,1

];

NoConcatenate

Final:

Load Month(Loss_date),Month(From_date),* Resident Table where Loss_date >= From_date And Loss_date <= To_date;

Drop table Table;

[upaliwije]

Thanks

But I want all records from table 1 and matching records from table 2 to be loaded

[anbu1984]

Table:

Load Policy_No,Premium,Product,Date#(From_date,'M/D/YYYY') As From_date,Date#(To_date,'M/D/YYYY') As To_date Inline [

Policy_No,Premium,Product,From_date,To_date

1,1,a,1/1/2014,2/1/2014

1,1,a,2/2/2014,3/1/2014

2,1,a,2/2/2014,3/1/2014

];

Join(Table)

Load Policy_No,Claim_No,Date#(Loss_date,'M/D/YYYY') As Loss_date,Claim_Paid Inline [

Policy_No,Claim_No,Loss_date,Claim_Paid

1,1,1/2/2014,1

];

NoConcatenate

Final:

Load Policy_No,Premium,Product,From_date,To_date,If(Loss_date >= From_date And Loss_date <= To_date,Claim_Paid) Resident Table;

Drop table Table;

[MarcoWedel]

Create marching table:

Intervalmatch(loss date, [policy no])

Load from_date, to_date, [policy no]

Resident table2;