SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query - Pulling Data from another table


Query - Pulling Data from another table

Author
Message
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 155
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
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45321 Visits: 10835
Could you provide the DDL for the tables?


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 155
What is DDL?
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45321 Visits: 10835
Data definition language. It's how you create the tables. For example, CREATE TABLE Bookings ...


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5850 Visits: 5279
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 )




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 155
Booking_Skey BookingNumber ArrivalDate DepartureDate BookingDate CancelledDate BookingValue PitchType_Skey Site_Skey
1 B00000001 2010-01-02 2010-01-23 2009-12-26 NULL NULL 4 2

PitchValues_Skey PitchType_Skey StartDate EndDate Value
4 4 2010-01-01 2010-12-31 20.00
11 4 2011-01-01 2011-12-31 21.60
18 4 2012-01-01 2012-12-31 22.70
25 4 2013-01-01 2013-12-31 22.70
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5850 Visits: 5279
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

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
AND Bookings.ArrivalDate BETWEEN PitchValues.StartDate AND PitchValues.EndDate --==========You probably need to add this condition
WHERE (Bookings.Booking_Skey = 1)




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 155
Hi Kingston

That's has worked thank you so much, I cannot believe it was one line of code.

Thanks again

Wayne
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5850 Visits: 5279
wafw1971 (2/21/2013)
Hi Kingston

That'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.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 155
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search