Lynn Pettis (12/17/2009)
Regarding the expected 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
Since John went on two tours, one in the North Region and one in the West, why is only the West tour reported? Didn't see anything in your criteria for that and just wanted to confirm that yo are only interested in each employee's most recent tour.
Edit: This is why...select '1','1','North','5/1/09','6/30/09' --igonore for the query result since tour ended for given date
That's a good point. I inferred from mak101's comment to ignore the first tour since it was closed that the desire was to include information only for tours that were in progress at the desired target date. That is the reason for...
AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)
I also made the assumption that it is okay for the tour to have ended on the target date.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.