Thanks Lynn and Bru for pointing me the direction. Here is my script to create tables and sample data, can you please guide me to get expected results?
--===== If the employees table already exists, drop it
IF OBJECT_ID('TempDB..#emplyees','U') IS NOT NULL
DROP TABLE #emplyees
--===== Create the employees table with
CREATE TABLE #employees(EmpID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmpName VARCHAR(30))
--===== If the EmpTours table already exists, drop it
IF OBJECT_ID('TempDB..#EmpTours','U') IS NOT NULL
DROP TABLE #EmpTours
--===== Create the EmpTours table with
CREATE TABLE #EmpTours(TourId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, EmpId INT,
Region VARCHAR(15), TourStartDate DateTime, TourEndDate DateTime)
--===== If the Orders table already exists, drop it
IF OBJECT_ID('TempDB..#Orders','U') IS NOT NULL
DROP TABLE #Orders
--===== Create the Orders table with
CREATE TABLE #Orders(OrderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
OrderDate DateTime, EmpId Int, OrderType char(1), OrderTotal Money)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Employees ON
--===== Insert the test data into the employees table
INSERT INTO #Employees (EmpID, EmpName)
SELECT '1','John'
UNION ALL
SELECT '2','Mike'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #employees OFF
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #EmpTours ON
--===== Insert the test data into the EmpTours
INSERT INTO #EmpTours (TourId, EmpID, Region, TourStartDate, TourEndDate)
select '1','1','North','5/1/09','6/30/09' --igonore for the query result since tour ended for given date
union all
select '2','1','West','8/15/09','' -- (on going)
union all
select '3','2','South','9/1/09','11/15/09'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #EmpTours OFF
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Orders ON
--===== Insert the test data into the Orders
INSERT INTO #Orders (OrderId, OrderDate, EmpID, OrderType, OrderTotal)
select '1','5/5/09','1','A',15000.00
union all
select '2','5/7/09','1','B',75000.00
union all
select '3','5/21/09','1','A',18000.00
union all
select '4','8/17/09','1','B',57000.00 --first order of John for the tour
union all
select '5','8/20/09','1','B',120000.00
union all
select '6','9/4/09','2','A', 32000.00 --first order of Mike for the tour
union all
select '7','9/10/09','2','B',81000.00
union all
select '8','9/17/09','1','A', 78000.00 -- last order of John before report date
union all
select '9','10/15/09','2','A',7000.00
union all
select '10','10/31/09','2','A', 13000.00 --last order of Mike before report date
union all
select '11','11/10/09','2','B',64500.00
union all
select '12','11/14/09','1','A',14500.00
union all
select '13','12/05/09','1','B', 77000.00
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Orders OFF