Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query - Pulling Data from another table Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 6:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, 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
Post #1422536
Posted Thursday, February 21, 2013 6:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 3,992, Visits: 3,014
Could you provide the DDL for the tables?


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1422540
Posted Thursday, February 21, 2013 6:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, Visits: 155
What is DDL?
Post #1422548
Posted Thursday, February 21, 2013 6:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 3,992, Visits: 3,014
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
Post #1422559
Posted Thursday, February 21, 2013 7:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1422564
Posted Thursday, February 21, 2013 7:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, 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
Post #1422573
Posted Thursday, February 21, 2013 7:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1422586
Posted Thursday, February 21, 2013 7:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, Visits: 155
Hi Kingston

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

Thanks again

Wayne
Post #1422592
Posted Thursday, February 21, 2013 7:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1422603
Posted Thursday, February 21, 2013 8:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, 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?
Post #1422609
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse