Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:

SQL
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
Posted
Updated 6-Jan-18 23:22pm
v2
Comments
F-ES Sitecore 6-Jan-18 12:36pm    
Try "SELECT DISTINCT". However without having access to your database schema or your data I don't know how you expect anyone to be able to help.

1 solution

Well, obviously you can visit more than one place on a tour, and have more than one activity, as well as more than one guide and so on.

So you need to do a Sum on PaymentOfGuide, TotalSpecialActivityCost and so on.
I would also reconsider doing this in a view, it's not what views are meant to do.
 
Share this answer
 
Comments
Maciej Los 7-Jan-18 5:22am    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900