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.
| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2011-10-26 : 05:36:36
|
| I have a table with a BookingNo(key) RoomId and a DateRequired, It has 28 time period fields P1 to P28My user selects a room and date - they then select a start time and end time eg P8 and P12. How can I write an sp that basically does this for any choice eg the next choice may be the same room and date P18 to P22Select * from MyTable where RoomId = @RoomIdDateRequired = @DateRequired andP8 = @P8P9 = @P9P10 = @P10P11 = @P11P12 = @P12 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-10-26 : 05:53:04
|
| Normalise it.Have a table for rooms -- Each room has a unique key and data about the roomhave a table for timeperiods -- each timeperiod has a unique key and data about that timeperiodhave a table for dates -- each data has a unique key and data about the datehave a table for bookingtimeperiods -- expand this table to have all possible combinations of timeperiodsHave a table for bookings -- bookings have a unique key and foreign keys to rooms, dates, bookingtimeperiodsCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-10-26 : 06:00:13
|
| Sorry - once you've normalised it then the stored proc should be pretty trivial.Look up the keys based on the parameters passed in and then just write a booking entry.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2011-10-26 : 06:01:40
|
| I have all those tables |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-10-26 : 07:04:05
|
You said:quote:
I have a table with a BookingNo(key) RoomId and a DateRequired, It has 28 time period fields P1 to P28
That doesn't sound like a normalised design. That table sounds like a spreadsheet.P18, 22 etc are the time periods?Each time period corresponds to a particular start time and end time? Then you could create a table that has all the possible combinations of periods (probably not that many) and have 1 key that represents each possible combination.Then when you get your start and end periods as parametersyou just look up the key for them and then insert a booking entryforroomNo \ Date \ TimePeriodKey \ Person......Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Does my sp have to be that long ?