Ok, here is where a little explanation may come in handy. If you look at my solution, the one comment I added, since I thought that would be the most confusing aspect, was the derived table. The solution is actually two queries, or a query within a query. You should google for "derived table". It is simply a query that acts like an object, and you can select from it, do calculations on it, join to it, etc. In SQL Server 2005 and above, a CTE does the same thing, and is much simpler to use. Anyhow, the derived table first gets the start and end dates, then you can get the other stuff by joining it back to #Orders. It may be confusing at first, but it will become second nature. To see what is happening, you can run the derived table by itself, like this...

DECLARE @date DATETIME

SELECT @date = '10/31/09'

SELECT

e.EmpId,

e.EmpName,

et.Region,

et.TourStartDate,

et.TourEndDate,

FOrdDate = MIN(o.OrderDate),

LOrdDate = MAX(o.OrderDate)

FROM #Employees e INNER JOIN #EmpTours et

ON e.EmpId = et.EmpId INNER JOIN #Orders o

ON e.EmpId = o.EmpId

WHERE et.TourStartDate <= @date

AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)

AND o.OrderDate BETWEEN et.TourStartDate AND @date

GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate

Once you have the dates, the rest is simply a matter of putting the order data in the right place. You may have wondered why I did this..

FOrdType = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType ELSE NULL END),

FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END)

To best understand it, it would be easiest to run the whole thing without using MAX() for the order data...

DECLARE @date DATETIME

SELECT @date = '10/31/09'

SELECT

t1.EmpName,

t1.Region,

t1.TourStartDate,

t1.TourEndDate,

t1.FOrdDate,

FOrdType = CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType ELSE NULL END,

FOrdTotal = CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END,

t1.LOrdDate,

LOrdType = CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType ELSE NULL END,

LOrdTotal = CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END

FROM

(--Derived table t1 returns the tourdates, and the order dates

SELECT

e.EmpId,

e.EmpName,

et.Region,

et.TourStartDate,

et.TourEndDate,

FOrdDate = MIN(o.OrderDate),

LOrdDate = MAX(o.OrderDate)

FROM #Employees e INNER JOIN #EmpTours et

ON e.EmpId = et.EmpId INNER JOIN #Orders o

ON e.EmpId = o.EmpId

WHERE et.TourStartDate <= @date

AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)

AND o.OrderDate BETWEEN et.TourStartDate AND @date

GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate

) t1 INNER JOIN #Orders o

ON t1.EmpId = o.EmpId

AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)

--GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate

Now you can see that in the results, you really have two sets of data. There are two rows with the FOrd information where the LOrd information is null, and there are two rows with the LOrd information where the FOrd information is null. The only way to put the FOrd and LOrd information on the same row is to use a GROUP BY, which means you are going to have to use an aggregate function on at least one of the columns. The MAX() serves this function, and it is really not doing anything other than making the GROUP BY happy. If you want to verify that it is doing nothing, change them to MIN(), and you will see that the results do not change. Now, I'm a trial and error kind of guy, so there are probably many better ways to explain this, but I don't know the terminology for a lot of this stuff. Maybe Lynn or Bru would be able to explain it better. Anyhow, if you have any questions, please let us know.