Thanks all of you working on it.

I was working hard on it and I finished it but not in one query, I dumped all data into #temp table and loop through cursor to find first and last order with other details, I know its long way but it worked. I have not tested Greg's code yet, but I wanted it something like that in one query.

As I mentioned earlier in my post

''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 I need tours details only if on given date tour is on. Sorry for any confusion.