Your query is basically joining the 1 row in Bookings table with 4 rows in PitchValues table

You will need one more condition on date to filter it down to one row

SELECTBookings.Booking_Skey, DATEDIFF(DAY, Bookings.ArrivalDate, Bookings.DepartureDate) * PitchValues.Value AS BookingValue, PitchValues.PitchType_Skey

FROMBookings

INNER JOIN PitchValues ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey

AND Bookings.ArrivalDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate --==========You probably need to add this condition

WHERE (Bookings.Booking_Skey = 1)