It's been a quiet long time this question posted but I just saw it. I tried to write something about this. I hope it is looking good.

declare @datex datetime

set @datex= '10/31/2009'

--print @datex

;with cte as(

select distinct

c.empid,

c.empname,

a.region,

a.TourStartDate,

a.TourEndDate,

o.orderDate FOrderDate,

o.OrderType FOrderType,

sum(o.OrderTotal) FOrderTotal

from #employees c

join #EmpTours a

on(c.EmpID=a.EmpID)

join #Orders o

on(c.EmpID=o.EmpId)

group by

c.empid,

c.empname,

a.region,

a.TourStartDate,

a.TourEndDate,

o.orderDate,

o.OrderType,

o.orderDate,

o.OrderType),

cte1 as(

select * from (

select empid,empname,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal, ROW_NUMBER() Over(Partition By tourstartdate

Order by [forderDate] asc) rn from cte

where forderdate> TourStartDate

) a

where rn=1)

select EmpName,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal,OrderDate LOrderDate,OrderType LOrderType,OrderTotal LOrderTotal from (

select

c.EmpID,c.EmpName,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal,o.OrderDate,o.OrderType,sum(o.OrderTotal) OrderTotal, ROW_NUMBER() Over(Partition By c.EmpID

Order by o.orderdate desc) rn

from cte1 c

join #Orders o

on c.empid=o.empid

where (@datex<c.TourEndDate or year(c.TourEndDate)=1900) and o.orderdate<=@datex

group by c.EmpID,c.EmpName,region,TourStartDate,TourEndDate,FOrderDate,FOrderType,FOrderTotal,o.OrderDate,o.OrderType) a

where rn=1