In here i wont to get only one row for each "TourId", but it gives multiple rows for each "TourId". how can i fix it?
What I have tried:
ALTER VIEW Bill
AS
SELECT Tour.TourId,
Itinerary.ItineraryId,
Tour.StartDate,
Tour.EndDate,
DATEDIFF(day,Tour.StartDate,Tour.EndDate) AS Duration,
Itinerary.EstTravelDist,
Guide.IdNo,
CAST(5000 * DATEDIFF(day,Tour.StartDate,Tour.EndDate) AS money) AS PaymentfoGuide,
SpecialActivity.Cost AS SpecielActivityCost,
VisitingPlaces.Cost AS VisitingPlaceTicketCost,
Tour.NumberOfPeople,
CAST( UnitPrice * Tour.NumberOfPeople AS money) AS CostForMeal,
Accommodation.Location AS Accomadation,
CAST(UnitPrice * NumberOfPeople * DATEDIFF(day,StartDate,EndDate) AS money) AS TotalAccommodationCost,
CAST(Itinerary.EstTravelDist * 40 AS money) AS TourPackegeCost,
SpecialActivity.Cost * Tour.NumberOfPeople AS TotalSpecielActivityCost,
VisitingPlaces.Cost * Tour.NumberOfPeople AS TotalVisitingPlaceTicketCost,
CAST(Itinerary.EstTravelDist * 40 + SpecialActivity.Cost * Tour.NumberOfPeople + VisitingPlaces.Cost * Tour.NumberOfPeople +
UnitPrice * DATEDIFF(day,Tour.StartDate,Tour.EndDate)
+ 5000 * Tour.NumberOfPeople * DATEDIFF(day,Tour.StartDate,Tour.EndDate) +
UnitPrice * DATEDIFF(day,Tour.StartDate,Tour.EndDate) * Tour.NumberOfPeople AS money) AS FINAL_COST
FROM (((((((((Itinerary
INNER JOIN SpecialActivity ON
Itinerary.ItineraryId = SpecialActivity.ItineraryId)
INNER JOIN VisitingPlaces ON
VisitingPlaces.ItineraryId = Itinerary.ItineraryId)
RIGHT JOIN Tour ON
Tour.TourId = Itinerary.TourId)
LEFT JOIN Guide ON
Guide.TourId = Tour.TourId)
INNER JOIN Vehicle ON
Vehicle.TourId = Tour.TourId)
INNER JOIN Accommodation ON
Accommodation.TourId = Tour.TourId)
INNER JOIN Participant ON
Participant.TourId = Tour.TourId)
INNER JOIN Person ON
Person.IdNo = Guide.IdNo)
INNER JOIN Contract ON
Contract.ItineraryId = Itinerary.ItineraryId)
SELECT * FROM Bill