SELECT	ED_Contact.C_MemberNum AS [Member #],
	ED_Contact.First_Name AS First,
	ED_Contact.Last_Name AS Last,
	ED_Contact.Address_1 AS Addr1,
	ED_Contact.Address_2 AS Addr2,
	ED_Contact.City,
	ED_Contact.State_ AS State,
	ED_Contact.Zip,
	ED_Alt_Phone.Phone,
	ED_Lead_.Phone AS [Other Phone],
	ED_Lead_.C_Phone_Type AS [Other Phone Type],
	ED_Alt_Phone_1.Email,
	ED_Lead_.Email AS [Other Email],
	ED_Lead_.C_Contact_Method AS [Pref. Ctct Method],
	ED_Lead_.C_Time_To_Contact AS [Pref. Ctct Time],
	ED_Lead_.C_Channel AS Channel,
	ED_Lead_.Lead_Source_Type AS [Marketing Source],
	ED_Contact_1.Rn_Descriptor AS [Ref. by Member],
	ED_Employee.Rn_Descriptor AS [Ref. by Employee],
	ED_C_Department.Rn_Descriptor AS [Ref. by Dept],
	ED_Employee_1.Rn_Descriptor AS [Created By],
	ED_C_Department_1.Rn_Descriptor AS [Create By Dept],
	ED_Lead_.Rn_Create_Date AS [Created On],
	ED_Lead_.C_Tr_Destination AS Destinations,
	ED_Lead_.C_Tr_DepartureDate AS [Departure Date],
	ED_Lead_.C_Tr_ReturnDate AS [Return Date],
	ED_Lead_.C_Tr_TravelDateText AS [Travel Date Notes],
	ED_Lead_.C_Tr_NumTravellers AS [Number Travelering],

CASE	WHEN Interest_Cruise.[Cruise2] = 'Cruise'
THEN	'Yes'
ELSE	' '
END AS Cruise,

	Interest_Cruise.[Cruise Qty] AS Qty,

CASE	WHEN Interest_Motor_Coach.[Motor Coach2] = 'Motor Coach'
THEN	'Yes'
ELSE	' '
END	AS [Motor Coach],

	Interest_Motor_Coach.[Motor Coach Qty] AS Qty,

CASE	WHEN Interest_Hotel.[Hotel2] = 'Hotel'
THEN	'Yes'
ELSE	' '
END	AS Hotel,

	Interest_Hotel.[Hotel Qty] AS Qty,

CASE	WHEN Interest_Air.[Air2] = 'Air'
THEN	'Yes'
ELSE	' '
END	AS Air,

	Interest_Air.[Air Qty] AS Qty,

CASE	WHEN Interest_Attraction_Tickets.[Attraction Tickets2] = 'Attraction Tickets'
THEN	'Yes'
ELSE	' '
END	AS Tickets,

	Interest_Attraction_Tickets.[Attraction Tickets Qty] AS Qty, 

	ED_Lead_.Comments

FROM	ED_Lead_

INNER JOIN ED_Contact 
ON ED_Lead_.Contact_Id = ED_Contact.Contact_Id 

INNER JOIN ED_Employee AS ED_Employee_1 
ON ED_Lead_.Created_By_Employee_Id = ED_Employee_1.Employee_Id 

INNER JOIN ED_C_Lead_Type 
ON ED_Lead_.C_Lead_Type_Id = ED_C_Lead_Type.C_Lead_Type_Id 

LEFT OUTER JOIN ED_C_Department AS ED_C_Department_1
ON ED_Lead_.C_Create_User_Dept_Id = ED_C_Department_1.C_Department_Id

LEFT OUTER JOIN ED_C_Department 
ON ED_Lead_.C_Refer_User_Dept_Id = ED_C_Department.C_Department_Id 

LEFT OUTER JOIN ED_Employee 
ON ED_Lead_.Referred_By_Employee_Id = ED_Employee.Employee_Id 

LEFT OUTER JOIN ED_Contact AS ED_Contact_1 
ON ED_Lead_.Referred_By_Contact_Id = ED_Contact_1.Contact_Id 

LEFT OUTER JOIN ED_Alt_Phone AS ED_Alt_Phone_1 
ON ED_Lead_.C_Email_Id = ED_Alt_Phone_1.Alt_Phone_Id 

LEFT OUTER JOIN ED_Alt_Phone 
ON ED_Lead_.C_Phone_Id = ED_Alt_Phone.Alt_Phone_Id 

--- Cruise
LEFT OUTER JOIN
(SELECT	Lead_Id,
	Rn_Descriptor AS [Cruise2],
	SUM(Quantity) AS [Cruise Qty]

   FROM ED_Product_Interest AS Interest1
   WHERE (Rn_Descriptor = 'Cruise')
   GROUP BY Lead_Id,
	    Rn_Descriptor)
   AS Interest_Cruise
ON ED_Lead_.Lead__Id = Interest_Cruise.Lead_Id

--- Motor Coach
LEFT OUTER JOIN
(SELECT	Lead_Id,
	Rn_Descriptor AS [Motor Coach2],
	SUM(Quantity) AS [Motor Coach Qty]

   FROM ED_Product_Interest AS Interest2
   WHERE (Rn_Descriptor = 'Motor Coach')
   GROUP BY Lead_Id,
	    Rn_Descriptor)
   AS Interest_Motor_Coach
ON ED_Lead_.Lead__Id = Interest_Motor_Coach.Lead_Id

--- Hotel
LEFT OUTER JOIN
(SELECT	Lead_Id,
	Rn_Descriptor AS [Hotel2],
	SUM(Quantity) AS [Hotel Qty]

   FROM ED_Product_Interest AS Interest3
   WHERE (Rn_Descriptor = 'Hotel')
   GROUP BY Lead_Id,
	    Rn_Descriptor)
   AS Interest_Hotel
ON ED_Lead_.Lead__Id = Interest_Hotel.Lead_Id

--- Air
LEFT OUTER JOIN
(SELECT	Lead_Id,
	Rn_Descriptor AS [Air2],
	SUM(Quantity) AS [Air Qty]

   FROM ED_Product_Interest AS Interest4
   WHERE (Rn_Descriptor = 'Air')
   GROUP BY Lead_Id,
	    Rn_Descriptor)
   AS Interest_Air
ON ED_Lead_.Lead__Id = Interest_Air.Lead_Id

--- Attraction Tickets
LEFT OUTER JOIN
(SELECT	Lead_Id,
	Rn_Descriptor AS [Attraction Tickets2],
	SUM(Quantity) AS [Attraction Tickets Qty]

   FROM ED_Product_Interest AS Interest4
   WHERE (Rn_Descriptor = 'Attraction Tickets')
   GROUP BY Lead_Id,
	    Rn_Descriptor)
   AS Interest_Attraction_Tickets
ON ED_Lead_.Lead__Id = Interest_Attraction_Tickets.Lead_Id

--- WHERE   (ED_Lead_.Rn_Create_Date >= @StartDate) AND (ED_Lead_.Rn_Create_Date < @EndDate + 1)
WHERE   (ED_Lead_.Rn_Create_Date >= getdate()-1) AND (ED_Lead_.Rn_Create_Date < getdate() + 1)
	AND (ED_C_Lead_Type.Rn_Descriptor = 'Group Travel - Individual')