February 21, 2013 at 6:29 am
I am getting a tad frustrated and was wondering if you can help:
I have a Pitch Values Table with the following Columns PitchValues_Skey, PitchType_Skey (this is a foreign key), Start Date, End Date and finally Value:
For Example:
1 7 01/01/2010 31/12/2010 £15
2 7 01/01/2011 31/12/2011 £20
And all I want to do is update my Bookings table with how much each booking is going to be, so I put together the code below which worked fine when I only had 2010 data, but I know have 2011 and 2012 and want to update it but it will only update with the 2010 prices.
SELECT Bookings.Booking_Skey, DATEDIFF(day, Bookings.ArrivalDate, Bookings.DepartureDate) * PitchValues.Value AS BookingValue,
PitchValues.PitchType_Skey
FROM Bookings INNER JOIN
PitchValues ON Bookings.PitchType_Skey = PitchValues.PitchType_Skey
WHERE (Bookings.Booking_Skey = 1)
So when I run the query above I would expect to see one line of data but instead I see 4 (See Below)
I would expect this:
Booking_Skey BookingValue PitchType_Skey
1 420 4
But I get this
Booking_Skey BookingValue PitchType_Skey
1 420 4
1 453.6 4
1 476.7 4
1 476.7 4
February 21, 2013 at 6:38 am
Could you provide the DDL for the tables?
February 21, 2013 at 6:48 am
What is DDL?
February 21, 2013 at 6:58 am
Data definition language. It's how you create the tables. For example, CREATE TABLE Bookings ...
February 21, 2013 at 7:03 am
Can you give us the results of the below mentioned queries
SELECT * FROM Bookings WHERE Booking_Skey = 1
SELECT * FROM PitchValues WHERE PitchType_Skey IN ( SELECT PitchType_Skey FROM Bookings WHERE Booking_Skey = 1 )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 21, 2013 at 7:16 am
Booking_SkeyBookingNumberArrivalDateDepartureDateBookingDateCancelledDateBookingValuePitchType_SkeySite_Skey
1B000000012010-01-022010-01-232009-12-26NULLNULL42
PitchValues_SkeyPitchType_SkeyStartDateEndDateValue
442010-01-012010-12-3120.00
1142011-01-012011-12-3121.60
1842012-01-012012-12-3122.70
2542013-01-012013-12-3122.70
February 21, 2013 at 7:35 am
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)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 21, 2013 at 7:47 am
Hi Kingston
That's has worked thank you so much, I cannot believe it was one line of code.
Thanks again
Wayne
February 21, 2013 at 7:57 am
wafw1971 (2/21/2013)
Hi KingstonThat's has worked thank you so much, I cannot believe it was one line of code.
Thanks again
Wayne
Great 🙂
Looking at your code, I have a question.
Which price should you take if your ArrivalDate falls in year 2010 and DepartureDate falls in the year 2011?
In our case both the dates fell in year 2010, so there were no issues
If you are sure that only the ArrivalDate needs to be considered, then the condition added is fine
But if its not, then you might have to change the date condition a bit to suit the requirement.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 21, 2013 at 8:04 am
Hi Kingston
I didn't think of that and I should have, each price is a daily price so if the price for a pitch is £9.00 on the 31/01/2010 and the price of the pitch is £9.70 for the 01/01/2011 then the total should be £18.70
And after looking at the data that has not happened.
How do we amend the code to compensate for that?
February 21, 2013 at 8:13 am
wafw1971 (2/21/2013)
Hi KingstonI didn't think of that and I should have, each price is a daily price so if the price for a pitch is £9.00 on the 31/01/2010 and the price of the pitch is £9.70 for the 01/01/2011 then the total should be £18.70
And after looking at the data that has not happened.
How do we amend the code to compensate for that?
This will be a bit complex
I will try and post a solution as soon as I get some time for that
Meanwhile I think somebody else might help as well..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 21, 2013 at 10:19 am
The below code will help you handle the overlapping conditions
SELECTBookings.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
FROMBookings
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply