Help - How to build this query
-
Hello everyone I am a bit new here, but hopefully you can help me.
First I shall provide a listing of the table structure we are working with.
Table1 : Booking
- BookNum (Primary Key)
Table 2: HotelSegment
- HotelSegNum (Primary Key)
- BookNum (Foreign Key - To Booking Table)
Table 3: HotelNight
- HotelNightID (Primary)
- HotelSegNum (Foreign Key - To HotelSegment Table)
- Price
Table 4: EventSegment
- EventSegNum (Primary)
- BookNum (Foreign Key - To Booking Table)
- Price
Table 5: AirSegment
- AirSegNum (Primary)
- BookNum (Foreign Key - To Booking Table)
- Price
Now for the data contained in those tables
Table1 : Booking
001
002
003
Table 2: HotelSegment
HotelSegNum | BookNum
001 | 001
002 | 003
Table 3: HotelNight
HotelNightID | HotelSegNum | Price
001 | 001 | 20.00
002 | 001 | 30.00
003 | 001 | 25.00
004 | 002 | 10.00
Table 4: EventSegment
EventSegNum | BookNum | Price
001 | 001 | 20.00
002 | 001 | 25.00
003 | 002 | 20.00
Table 5: AirSegment
AirSegNum | BookNum | Price
001 | 001 | 100.00
002 | 002 | 100.00
003 | 003 | 100.00
Results that I want to obtain........(as long as my math is accurate)
ResNum | Price
001 | 220.00
002 | 120.00
003 | 110.00
Thanks for all of your help!
-
Assuming Resum is the same as BookNum that appears in most of the tables:
SELECT BookNum,X.PRICE + Y.PRICE + Z.PRICE AS PRICE
FROM HotelSegment
LEFT OUTER JOIN (SELECT BookNum ,SUM(Price) AS PRICE FROM HotelNight INNER JOIN HotelSegment ON HotelSegment.HotelSegNum=HotelNight .HotelSegNum GROUP BY BookNUM) X
ON HotelSegment.BookNum = X.BookNum
LEFT OUTER JOIN (SELECT BookNum, SUM(Price) AS PRICE FROM EventSegment GROUP BY BookNum) X
ON HotelSegment.BookNum = Y.BookNum
LEFT OUTER JOIN (SELECT BookNum, SUM(Price) AS PRICE FROM AirSegment GROUP BY BookNum) X
ON HotelSegment.BookNum = Z.BookNum
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Is Resnum from Table1 and what you want is a sum of all the charges asscoiated with that reservation number?
select b.resnum,
(sum (hn.price) + sum(ev.price) + sum(ai.price)) as totalprice
from booking b
left join hotelsegment hs on b.resnum = hs.booknum
left join hotelnight hn on hs.hotelsegnum = hn.hotelsegnum
left join eventsegment ev on ev.booknum = b.resnum
left join airsegment ai on ai.booknum = b.resnum
group by b.resnum
order by b.resnum
-
"Assuming Resum is the same as BookNum that appears in most of the tables:"
yes i apologize, ResNum should be the link to all tables marked as "book num"
thanks to both of you, going to try and test these out asap!
-
Hi ,
Try this Query its working.....
select B.booknum , isnull(b.price,0) + isnull(ES.price,0) + isnull(Aseg.price,0) from (
select B.booknum , sum (isnull(Hn.Price,0)) Price
from booking b
left outer join
(select booknum , price from HotelSegment Hs Join HotelNight Hn
on Hn.hotelsegnum=hs.hotelsegnum)Hn
on Hn.booknum=B.booknum
group by B.booknum ) b
left outer join
(select booknum ,sum (isnull(Price,0)) Price from EventSegment
group by booknum
)ES
on
b.booknum=es.booknum
left outer join
(select booknum ,sum (isnull(Price,0)) Price from AirSegment
group by booknum
) Aseg
on
b.booknum=Aseg.booknum
Group by B.booknum,b.price , ES.price ,Aseg.price
Regards,
Amit Gupta
-
And... he's learned nothing because none of you asked him to show what he tried on what is very likely to be a homework problem...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
most likely true now that i re-read the question; like almost of your posts, I try to give a solid working answer on a question, when it is worthy...but i think i have trouble picking out homework assignments from the newbie questions. Thanks Jeff;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
Heh... Not to worry, Lowell... as always, your response shows the good person you are.
The give away is normally a question laid out so well that if the person writing the post could actually write the original question that way, they would also be able to answer the question.
Even if the question is a bit more "newbie" in nature, I will, many times, say "Sure, I can help... but what have you tried?" Trying and learning, even for veterans of SQL, go hand in hand (most of the vets know that).I really feel sorry for folks that blindly get answers for homework (or even work-work) this way... most really do learn nothing (there are the occasional rare souls that actually study the answer to figure out what was done). I'd much rather someone say, "I've got this homework problem, here's the table layout, here's the data, the expected output, and here's what I've tried. I understand that yada-yada but I'm having difficulty with yada-yada. Can you point me in the right direction ... etc, etc." For that matter, I wish people writing SQL for a living would do it that way, too,
instead of the ol' "require an answer".'Course, this is nothing new... it's a subject that's been written about a hundred times on this forum so sorry for the rhetoric.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
for the record it isnt homework at all. I was having a hard time to get this information subtotaled in this way - and efficiently. The suggestions here have at least helped me get the information subtotaled correctly for each individual record.
Previously I was doing this in a function similar to this
--Hotel Price
select @TempPrice = Sum(NightGrossPRice) FROM HotelNights Where ResNumber = @pResNumber
if @TempPrice is NOT NULL
Set @gross = @gross + @TempPrice
--Air Price
select @TempPrice = Sum(Price) FROM AirSegments Where ResNumber = @pResNumber
if @TempPrice is NOT NULL
Set @gross = @gross + @TempPrice
...
and when running this function for a single reservation it worked perfectly - but when trying to use this function when selecting multiple rows on the reservation table ( Select *, dbo_getgross(ResNumber) from reservations) it would take way to long to calculate.
unfortunately even though the queries here are definately more efficient and without a doubt a much better way to do it - but the time to return not even 1,000 rows seems to take much longer then I would expect.
-
thats actually what I am looking into right now - I somewhat assumed that these indexes existed from those that built this structure
-
Thanks for the feedback, David... you've restored my faith... sorry for the misread.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
David,
You list a certain column (Seg or ID, ususally) in each of your tables as a Primary or Primary Key... are they actually Primary Keys with a PK constraint in place? Some folks create a Unique Index with a "PK" in it and think that makes a Primary Key... it doesn't.
The other thing I'm concerned about is the data... do the ID's really have leading zeroes and are they of the CHAR or VARCHAR datatype? What data type are they?
And, what about the price columns? What is their data type?
Last but not least, what is the scale of each table... that is, approximately how many rows are there in each table?
I'm actually going somewhere with all of these questions... I need to know so I can build a large scale example so we can "play" a bit with some tuning both by code and by index. It's the least I can do now that I've seen what you've tried (thanks for that).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
- well that wasnt much fun at all - I just posted this once and the post apparently didnt work.
Anyway here it is again.......
Question 1: do i really have primary keys
- yes the columns i list as primary keys are selected as a primary key in the table, datatype of integer, identity and autonumbered (forgive my 'newbieness' but the fact that a key is shown at this column indicates that it is definately a primary key not simply the index correct?)
Question 2: do ids really have Zeros
-no they dont they are in fact integer values, to somewhat simplify this issue, I removed some columns and tables that did not seem to be very relevant to the issue, and typed up example data to try and make it clear what I had and needed, the added zeros was simply a typing error on my part.
The datatype of the price columns is set to 'Money'
Question 3: Table scale
well in my eyes it seemed rather small - and I do see it growing a bit which is why i was concerned with the time it took to retrieve this calculation to start. At the time of this post the tables are as follows
Booking - 8,306 records
HotelSegment - 8187 records
HotelNights - 25688 records
EventSegment - 19690 records
AirSegments - 0 records
I modified the first query provided by Lowell as well.
- I assumed that each new join he meant to use X,Y,Z as he used in the Addition above, so made this change
- I put a ISNULL(X.Price,0) on each of the price values because i seem to recall that when adding a NULL value it messed up this calculation and it is very likely that one of the joined tables will have no records and thus no sum
- I made the first table be my booking table, the query looked as though it depended on a hotelsegment to join the rest, and all of these tables may or may not have items in them depending on the specific booking - in other words i cant depend on the hotel segment being there to get a booking price for something that only contains event segments.
- well that wasnt much fun at all - I just posted this once and the post apparently didnt work.
-
Heh... yeah... the site has a "timeout" for some reason... you should always copy the entire body of your post before you hit [Post Reply] so you don't loose anything.
The "key" showing up on the columns does, in fact, mean that it's a real Primary Key...
Overall, you are correct... this is a small set of rows and the return should be almost instantaneous... I'll see what I can do... gotta build some test data, first. On my way to just now so I'll have to hit it tonight.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply