Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi peronist,
you can use condition like Ord.Date >= Startdate and Ord.Date <= Enddate
Hope it helps you.
Regards
Pradip
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.
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