What I need is, on a given date which employees were on tour and which region? Along with it, I need their tour start date, tour end date, first order of the tour and last order of ther tour with order date and order totals.

So based on my rules and sample data for given date 10/31/09 I should get following results:

EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt

John West 8/15/09 - 8/17 B 57K 9/17 A 78K

Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K