SELECT	Bookings.Booking_Skey, 
	SUM ( 
		(
		DATEDIFF(
				DAY,
				CASE
					WHEN PitchValues.StartDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate THEN PitchValues.StartDate
					ELSE Bookings.ArrivalDate
				END,
				CASE
					WHEN PitchValues.EndDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate THEN PitchValues.EndDate
					ELSE Bookings.DepartureDate
				END
			) + 1
	        ) * PitchValues.Value
	    ) AS BookingValue, PitchValues.PitchType_Skey
FROM	Bookings
INNER JOIN PitchValues
	ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey
	AND ( 
		Bookings.ArrivalDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate OR
		Bookings.DepartureDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate OR
		PitchValues.StartDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate OR
		PitchValues.EndDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate
	)
WHERE (Bookings.Booking_Skey = 1)
GROUP BY Bookings.Booking_Skey, PitchValues.PitchType_Skey