June 16, 2011 at 4:05 pm
So let me see the experts here .... 🙂
I have a table :
"ROOM_PRICES"
Room_type
start_date
end_date
room_price
I am trying to run a query against the second table so it gives me
room_price based on room_type and datetimepicker values.
(So basically,all 3 values are parameters.For room_type I use and edit box..)
What would be the right syntax?
I want the query to give me the right price exclussively
within the date range selected in datetimepickers (not the price range entered in the database).
Example:
I select 1.1.2011 as start_date and 12/01/2011 as end_date.
However on 10.1.2011 prices change. So my queery would return me :
start_date end_date days room_price
1.1.2011 10.1.2011 10 40
10.1.2011 12/01/2011 2 60
Any help much much appreciated ....
June 16, 2011 at 4:11 pm
Yep, i know how to do this; But i want more information from you. I need to get the DDLs, DMLs for samples tables, sample table data , indexes etc..Please proivde them and then u ll see the magic of lot of VIPs coding for you :0 i see you are very new to SSC 🙂
June 16, 2011 at 4:28 pm
See the first link in my signature for what CC's asking for. Help us help you, we don't want to have to build our own sample testbeds to show you the code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 16, 2011 at 9:50 pm
Basic table design is very simple. I have 2 tables ROOM_TYPES (which contain only ROOM_TYPE (string) and ROOM_PRICES (string) )
Example of the data would be (ROOM_TYPES):
//ROOM_TYPE // ROOM_DESCRIPTION//
//DBL // double room //
//SGL // single room //
etc.
The other table (ROOM_PRICES) :
//ROOM_TYPE //START_DATE//END_DATE //ROOM_PRICE//
// DBL // 03.01.2011 // 16.04.2011// 80,00 //
// DBL // 22.10.2011 // 28.12.2011// 60,00 //
// DBL //16.04.2011 // 25.04.2011// 70,00 //
// SGL // 30.07.2011 // 20.08.2011// 63,00 //
etc.
Like you might have guessed ROOM_TYPE is the connecting factor between the tables. However,I do not JOIN them at this stage because I do not need to. I have a lookupcombo that retrieves the data from that table (ROOM_TYPES) so it is basically used just to select room_type that I would require the price off.
Now that I have the room_type selected,I run the queery against the second table (ROOM_PRICES) with lookupcombo text as the first parameter (ROOM_TYPE).
SELECT * FROM ROOM_PRICES WHERE ROOM_TYPE = :a1 AND ...
This second part is the one that gets me confused.
I select START_DATE and END_DATE using datetimepickers
(Parameters :a2 and :a3)
Now the queery result I wish to obtain is the following:
Example : I select in my lookupcombo ROOM_TYPE which is DBL
Then I pick the dates:
START_DATE = 01.03.2011
END_DATE = 18.04.2011
As you can see (sample data) theres a change in price for this type of room.
So ,I would like my queery to display :
//ROOM_TYPE//START_DATE //END_DATE//DAYS//PRICE_PER_DAY//
//DBL //01.03.2011 // 16.04.2011 //47 // 80,00//
//DBL //16.04.2011 // 18.04.2011 // 2 // 70,00 //
I hope you understand me ...
June 16, 2011 at 10:41 pm
uhh..forgot to tell you....I am using Access database.
Since this is used in connection to programming , the way I pass my parameters to the queery :
ADOQuery1.Parameters.ParamByName('a1').asString := lookupcombobox1.text;
etc...
SQL is the same...
June 16, 2011 at 10:43 pm
My SQL is a little rusty .... 🙂
June 17, 2011 at 7:40 am
skynet_si (6/16/2011)
uhh..forgot to tell you....I am using Access database.Since this is used in connection to programming , the way I pass my parameters to the queery :
ADOQuery1.Parameters.ParamByName('a1').asString := lookupcombobox1.text;
etc...
SQL is the same...
uhh...forgot to mention this is a sql server forum. The logic and syntax will be totally different (and WAY slower) in Access. You will probably have better luck on an Access forum.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2011 at 8:25 pm
syntax is the same.
this is basic sql ....
June 17, 2011 at 8:28 pm
or you can provide me with sql server version too ...
I plan to move it there anyway so ...
😀
June 18, 2011 at 4:02 am
June 19, 2011 at 2:22 pm
You're joking .... right ???
June 19, 2011 at 2:23 pm
Did you read the q. ?
June 20, 2011 at 6:59 am
skynet_si (6/19/2011)
Did you read the q. ?
Reading the question was the easy part. The hard part was reading your mind. As ColdCoffee asked in the first post we need some details. ddl, sample data etc. Read the link in my signature for best practices on posting questions. Remember we are all volunteers on here and we can't see over your shoulder or have any knowledge of your project. If you help us help you, you will get tested code and results.
Your comment about access and sql server syntax being the same is quite funny. There are so many things that access can't do and your problem is very likely one of them. You are most likely going to have process this row by row in the front end.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2011 at 3:13 pm
I have given you all the info. & data you need.
Also,I know the diff. between Access and SQL server sql...
All I am asking is how should the query go.:-)
June 20, 2011 at 3:24 pm
skynet_si (6/20/2011)
I have given you all the info. & data you need.Also,I know the diff. between Access and SQL server sql...
All I am asking is how should the query go.:-)
Well all of the info except for datatypes, column names and sample data in a format that makes it easy for the people who are offering their time to you for free. Let's try this again.
I have a pretty good idea of what you are after but in Access you are going to have to do this row by row for each room type. I think you are looking for a listing of each room type and the number of days it was at the corresponding price rate given a start and end date?
I don't know of any way to do this easily in Access other than looking at each room type and then determining the price ranges for that room type. Repeat this for each room type. I don't know of any way to get this kind of data from a single select statement in Access.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply