tsql - Pulling correct results from my PitchValues Table -

tsql - Pulling correct results from my PitchValues Table -

i getting tad frustrated , wondering if can help:

i have pitch values table next columns pitchvalues_skey, pitchtype_skey (this foreign key), start date, end date , value:

for example:

1 7 01/01/2010 31/12/2010 £15 2 7 01/01/2011 31/12/2011 £20

and want update bookings table how much each booking going be, set code below worked fine when had 2010 data, know have 2011 , 2012 , want update update 2010 prices.

select bookings.booking_skey, datediff(day, bookings.arrivaldate, bookings.departuredate) * pitchvalues.value bookingvalue, pitchvalues.pitchtype_skey bookings inner bring together pitchvalues on bookings.pitchtype_skey = pitchvalues.pitchtype_skey (bookings.booking_skey = 1)

so when run query above expect see 1 line of info instead see 4 (see below)

i expect this:

booking_skey bookingvalue pitchtype_skey 1 420 4

but this

booking_skey bookingvalue pitchtype_skey 1 420 4 1 453.6 4 1 476.7 4 1 476.7 4

all sorted now, help.

select bookings.booking_skey, datediff(day, bookings.arrivaldate, bookings.departuredate) * pitchvalues.value bookingvalue, pitchvalues.pitchtype_skey bookings inner bring together pitchvalues on bookings.pitchtype_skey = pitchvalues.pitchtype_skey , bookings.arrivaldate between pitchvalues.startdate , pitchvalues.enddate (bookings.booking_skey = 1)

tsql