Query with a big function..advice...ideas...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query with a big function..advice...ideas...

Author

Topic

Gnub
Starting Member

7 Posts

Posted - 2007-03-27 : 10:54:18

Ok, im fairly new to SQL (real world use) uni wasn't all that, so im throwing out my idea for some feedback, and to see if it is at all possible.

I have 2 tables, 1 is called Hotels, and 1 is called Flights. I want...to list every Hotel, with price, availability date, and Flight date from a (GetDate()) function, up untill a date specified in a query.

At first this sounds easy. However... a hotel can be available for up to a month or two at a time, so at least 60 records for 1 hotel.

In this query i want a SUM of the Hotel price between the GetDate() date, and the end date specified in the query.

Can this all be done in 1 SQL query?

Thanks in advance for any input you may provide.

Gnub

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 10:57:27

We will need some more details

Read the hnt link in my sig and post what it asks for

But basically, DDL, sample data and the expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 10:58:15

Maybe. It depends.
Seems like a project too large for you with your limited knowledge in T-SQL language.

Peter Larsson
Helsingborg, Sweden

Gnub
Starting Member

7 Posts

Posted - 2007-03-27 : 11:21:35

X002548:

Hotels:
Name: AvailableFrom: EndAvail: Price:
Hotel1, 01/01/2007, 01/03/2007, Ranges from £20 - £36
Hotel2, 01/01/2007, 05/02/2007, £25
Hotel3, 01/02/2007, 06/03/2007, Ranges from £20 - £28

Flights:
FlightNumber: FlightDate: FlightTime:
XX205, 01/01/2007, 09:00
...
XX599, 06/06/2007, 17:25

Proposed Query:
Bring back all the Hotels from now untill 05/02/2007, with flight time per Hotel Availability date (starting from now), and sum the price from now untill 05/02/2007

05/02/2007 = Made up date for sake of query.
now = GetDate()

lot to think about i know...trying to figure out the best way to do it. Rather it be done in 1 big query, than in smaller queries.

thanks in advance again.
Gnub

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 11:26:14

Good luck Brett!

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 11:27:40

Gonna be hard to SUM any prices that look like:

Ranges from £20 - £28

Did you read the hint link in my sig?

So, you want all the flights that will get you to available hotels for your date range?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Gnub
Starting Member

7 Posts

Posted - 2007-03-27 : 11:28:24

to help...
*smiles*

SELECT * FROM HOTELS

Gnub
Starting Member

7 Posts

Posted - 2007-03-27 : 11:33:01

Ok, to simplify it:

Hotel1, 01/01/2007, 01/02/2007, £20
Hotel1, 01/02/2007, 01/03/2007, £25
Hotel2, 01/01/2007, 05/02/2007, £25
Hotel3, 01/02/2007, 06/02/2007, £20
Hotel3, 07/02/2007, 26/02/2007, £24
Hotel3, 27/02/2007, 06/03/2007, £28

Query:
Return all from now(for sake of argument...) 20/01/2007 untill 02/03/2007. With that data, bring the Flight date and Time, also SUM the Price for each hotel between those dates.

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 11:36:31

quote:
Originally posted by Peso

Good luck Brett!

Peter Larsson
Helsingborg, Sweden

well, don't you think it's gonna be something like

FROM HOTEL h JOIN FLIGHTS f ON f.date between h.startdate and h.endate

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Gnub
Starting Member

7 Posts

Posted - 2007-03-27 : 11:38:42

What's picking my brain, is how to SUM the prices between the dates.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 11:40:12

I agree. I was more thinking of all the other stuff, like the SUM thingy of strings.
And I am pretty sure there will be some more requirements on the way soon...

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 11:43:17

Not unless s/he reads the hint link and post what it asks for...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Gnub
Starting Member

7 Posts

Posted - 2007-03-27 : 12:08:01

Ok, let's try again, with something a little easier. with the SUM part of the query.

I dont belive the DDL is required. im not asking for the whole query to be given to me on a plate. It's not learning if your spoon fed. Pointers, tips, is all im asking for. :)

Return * from Hotels WHERE Date = GetDate(Which is 02/01/2007)(SUM Price from GetDate and 02/01/2007)

What's highlighted in red, how would i go about doing that?

Sample:
HotelName: Date: Price:
Hotel1 01/01/2007 25
Hotel1 02/01/2007 25
Hotel1 03/01/2007 26
Hotel1 04/01/2007 26
Hotel1 05/01/2007 24
Hotel1 06/01/2007 25
Hotel1 07/01/2007 25
Hotel1 08/01/2007 26
Hotel1 09/01/2007 26
Hotel1 10/01/2007 23
Hotel2 02/01/2007 22
Hotel2 03/01/2007 22
Hotel2 04/01/2007 22
Hotel2 05/01/2007 22
Hotel2 06/01/2007 22
Hotel2 07/01/2007 22
Hotel2 08/01/2007 22
Hotel2 09/01/2007 22
Hotel2 10/01/2007 22
Hotel2 11/01/2007 22
Hotel2 12/01/2007 22
Hotel2 13/01/2007 22
Hotel3 02/01/2007 21
Hotel3 03/01/2007 22
Hotel3 04/01/2007 35
Hotel3 05/01/2007 22
Hotel3 06/01/2007 33
Hotel3 07/01/2007 33
Hotel3 08/01/2007 33
Hotel3 09/01/2007 21
Hotel3 10/01/2007 21
Hotel3 11/01/2007 21

Expected Result:
Hotel1 02/01/2007 177
Hotel2 02/01/2007 176
Hotel3 02/01/2007 199

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 12:15:09

select sum(price) from table where date between '20070201' and current_timestamp

Peter Larsson
Helsingborg, Sweden

Gnub
Starting Member

7 Posts

Posted - 2007-03-27 : 12:16:28

great! thanks.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-27 : 12:19:19

quote:
Originally posted by Gnub

Return * from Hotels WHERE Date = GetDate(Which is 02/01/2007)(SUM Price from GetDate and 02/01/2007)

what on earth kind of sql dialect is that?

www.elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 12:29:44

What happened to the Flights table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources