Help Sql select

[#1]

Dear all,

I'm facing the follwing problem:

Table1                                               

Order Nr   Ord.Date CampaignCode 

100          01-02-12     PROMO

101          15-04-12     PROMO

Table2

CampaignCode  Order nr.  other infor concerning Campaign    

PROMO               100        other data

PROMO               101        ....

Table3

Campaigncode      Customer     Start / End Date

PROMO               A            01-01-12  31-03-12

PROMO               A            01-01-12  31-03-12

PROMO               B            01-01-12  30-04-12

I need to get all the records where Order date is included into the Campaign start/end date, in  this case the first and last line.

How can I solve it by an sql select ?

Help please

Thanks in advance for your help

Gr.

Stefan

[#2]

Hi Stefan,

The easiest way is joining the three tables to one and the loading all dates with the where-condition Ord.Date >= Startdate AND Ord.Date <= Enddate.

Or you use the IntervalMatch.

Both you cann see in the attached file.

Regards Vicky

[senpradip007]

Hi peronist,

you can use condition like Ord.Date >= Startdate and Ord.Date <= Enddate

Hope it helps you.

Regards

Pradip

[#4]

Hi Vicky,

I'm working with the personal edition and I'm not able to open your file. Could you put your script here, please ?

Thanks very much !!

Gr.

[#5]

Good Morning Stefan,

Here you are

Table1:

LOAD * INLINE [
    Campaign, Costumer, Startdate, Enddate
    PROMO, A, 01.01.2012, 31.03.2012
    PROMO, A, 01.03.2012, 31.03.2012
    PROMO, B, 01.01.2012, 30.04.2012
    PROMO, C, 01.06.2012, 31.08.2012
    PROMO, D, 01.09.2012, 31.10.2012
    PROMO, D, 01.03.2012, 30.06.2012
    PROMO, E, 01.05.2012, 31.07.2012
];

Table2:
LOAD * INLINE [
    Order Nr, Ord.Date, Campaign
    100, 01.02.2012, PROMO
    101, 15.04.2012, PROMO
    102, 02.03.2012, PROMO
    103, 06.06.2012, PROMO
    104, 05.09.2012, PROMO
    105, 07.10.2012, PROMO
    106, 05.04.2012, PROMO
];
left join IntervalMatch ([Ord.Date]) LOAD Startdate, Enddate resident Table1;

I hope this is want you want.

Regards

Vicky