Hi,
I hope somebody of you could help me here. I have the requirement to find the next working day 2 days after the visit. I need to join two tables which have an N to N relation.
This is the first source table tbl_cust with customer and visiting day:
customer_id Name visit_day company_id
1 Tobias 27.08.2011 100
2 Hans 30.08.2011 100
3 Lars 27.08.2011 200
4 Dave 29.08.2011 200
The second table tbl_comp_days includes the days where the different companies work:
company_id company_day working_day (yes/no)
100 27.08.2011 no
100 28.08.2011 no
100 29.08.2011 yes
100 30.08.2011 yes
100 31.08.2011 yes
100 01.09.2011 yes
200 27.08.2011 yes
200 28.08.2011 yes
200 29.08.2011 no
200 30.08.2011 yes
200 31.08.2011 yes
200 01.09.2011 yes
Now I want to calculate the second next work of the company joined with the customer visit_day
Means: tbl_cust.visit_day + 2
when tbl_comp_days.working_day is yes
if tbl_cust.visit_day is not a working day find the next possible working day + 2 working days
Output example:
customer_id Name visit_day visit_day_plus_2
1 Tobias 27.08.2011 31.08.2011
2 Hans 30.08.2011 01.09.2011
3 Lars 27.08.2011 30.08.2011
4 Dave 29.08.2011 01.09.2011
Thanks for your help
Tobias