September 2, 2022 at 9:45 pm
I agree with Scott that such a query is going to return all the hotel rooms except "some" and will likely do a scan anyway. So, just keep it simple, like Phil did. No need for Integer division or any of the other tricks of the trade.
I'll also add that if each floor has less than 101 rooms per floor, an INT will work just fine well past the height of any building currently in existence or will be in existence for the next millennia.
And, seriously... how many times are you actually going to write a query that excludes just on floor out of all floors? It would be better just to mark them as unavailable if you want to exclude them for reasons of construction or whatever.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2022 at 9:58 am
Glad I've got Steve on my side :), felt like I was swimming against the tide!
I was trying to move the conversation on to be generic, but if we are talking specifics, yes if all the hotels in your chain have less than 10 floors and no rooms on the ground floor or in basement floors, stripping the first character from the room number works (assuming you have less than 100 rooms) to derive the floor, otherwise you would actually need to strip the last two as the room identifier and keep the remainder as the floor number or replace it with 0 if it is an empty string.
Personally I think it would just be easier to (at design time) do that calculation into a pair of computed columns and keep the resulting query logic clean in the 1000+ scripts you subseqently write.
Plus you can idex them separately if you need to. just sayin..
September 3, 2022 at 10:01 am
how about:
give me a list of hotels of 4 or more floors where any floor has more than 12 rooms?
Not a particularly difficult query, but made much simpler if you can do max(floornumber) and max(roomidentifier) on the two computed columns.
September 3, 2022 at 5:44 pm
how about:
give me a list of hotels of 4 or more floors where any floor has more than 12 rooms?
Not a particularly difficult query, but made much simpler if you can do max(floornumber) and max(roomidentifier) on the two computed columns.
Agreed and, just to offer yet another alternative, since hotels aren't known for adding or removing rooms from a physical structure, a bit of pre-aggregation for such hotel attributes would be an even bigger help. This could take the form of a separate table or an indexed view.
And, to be sure, I didn't mean to sound negative about the ideas you presented. "It Depends" is what I was trying to get across and should have stated early on. For example, is this an exercise in a study book, and interview question, or something real and, if real, it would also be interesting if the OP would explain why they wanted to do such a query. If it's something common, then I definitely support the idea of splitting the hotel room into the parts you suggested.
The other thing is, I love discussions like this and this site makes it possible. There are a lot of sites that are SO (pun intended) against the idea of exploration into the original need and just want you to provide an answer for the question the OP asked even if the idea of the question may be one of the worst things you could do.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2022 at 10:53 pm
I have to say, in this case I would use a char/varchar column for room numbers, not an int. Even if the hotel guest rooms are all numeric, sometimes other rooms are not (meeting rooms, etc.). Also, I believe I have stayed in hotels where mezzanine room started with 'M'. As noted earlier, the rooms themselves should be consistent 2 or 3 digits (or chars), with the floor preceding that, such as 301 or 1214 (for hotels that have a 12 floor, obviously).
Naturally you can use a computed column(s) to store separate floor and room columns, if you prefer.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 4, 2022 at 7:47 pm
I have to say, in this case I would use a char/varchar column for room numbers, not an int. Even if the hotel guest rooms are all numeric, sometimes other rooms are not (meeting rooms, etc.). Also, I believe I have stayed in hotels where mezzanine room started with 'M'. As noted earlier, the rooms themselves should be consistent 2 or 3 digits (or chars), with the floor preceding that, such as 301 or 1214 (for hotels that have a 12 floor, obviously).
Naturally you can use a computed column(s) to store separate floor and room columns, if you prefer.
If I were designing this, I'd use strings as well, and separate floor from room identifier. Easier to combine them in results, and filter for them in queries.
September 4, 2022 at 9:14 pm
ScottPletcher wrote:I have to say, in this case I would use a char/varchar column for room numbers, not an int. Even if the hotel guest rooms are all numeric, sometimes other rooms are not (meeting rooms, etc.). Also, I believe I have stayed in hotels where mezzanine room started with 'M'. As noted earlier, the rooms themselves should be consistent 2 or 3 digits (or chars), with the floor preceding that, such as 301 or 1214 (for hotels that have a 12 floor, obviously).
Naturally you can use a computed column(s) to store separate floor and room columns, if you prefer.
If I were designing this, I'd use strings as well, and separate floor from room identifier. Easier to combine them in results, and filter for them in queries.
I'd likely do similar but with TinyInt for each. There'd also be a usage category column.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2022 at 11:38 pm
Steve Jones - SSC Editor wrote:ScottPletcher wrote:I have to say, in this case I would use a char/varchar column for room numbers, not an int. Even if the hotel guest rooms are all numeric, sometimes other rooms are not (meeting rooms, etc.). Also, I believe I have stayed in hotels where mezzanine room started with 'M'. As noted earlier, the rooms themselves should be consistent 2 or 3 digits (or chars), with the floor preceding that, such as 301 or 1214 (for hotels that have a 12 floor, obviously).
Naturally you can use a computed column(s) to store separate floor and room columns, if you prefer.
If I were designing this, I'd use strings as well, and separate floor from room identifier. Easier to combine them in results, and filter for them in queries.
I'd likely do similar but with TinyInt for each. There'd also be a usage category column.
Tinyint will not work well if you ever have room# m-121 (for mezzanine). I've stayed in rooms named that way.
And meeting rooms often have no number at all, but are instead indicated by name: "Lincoln A", "Sunset Room [A]", etc..
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 5, 2022 at 7:59 pm
Jeff Moden wrote:I'd likely do similar but with TinyInt for each. There'd also be a usage category column.
Having been to Las Vegas in the last few years, not sure a Tinyint would work 😉
That IS a possibility!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2022 at 8:14 pm
Heh... checking out the following link...
https://financesonline.com/10-biggest-hotelst-in-the-world-the-highest-number-of-rooms-to-stay-in/
... The entries that list the number of rooms, floors, and buildings seem to indicate that TinyInt will probably work just fine. You would need an extra column for the building number, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 6, 2022 at 6:49 am
This was removed by the editor as SPAM
September 6, 2022 at 3:47 pm
I'd strongly urge you to avoid a tinyint. Mainly because the room number is "904", not just 4 (and not 94). If you think about it, "room 4" is meaningless, you must have the full room number.
Also, some guest room numbers may have letters in them.
And meeting rooms at hotels are traditionally named, such as "Apple Blossom" or "Jefferson A", "Jefferson B", etc..
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 6, 2022 at 6:11 pm
I'd strongly urge you to avoid a tinyint. Mainly because the room number is "904", not just 4 (and not 94). If you think about it, "room 4" is meaningless, you must have the full room number.
Also, some guest room numbers may have letters in them.
And meeting rooms at hotels are traditionally named, such as "Apple Blossom" or "Jefferson A", "Jefferson B", etc..
Simple. It doesn't. That would be a part of the description for the room.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2022 at 4:25 pm
ScottPletcher wrote:I'd strongly urge you to avoid a tinyint. Mainly because the room number is "904", not just 4 (and not 94). If you think about it, "room 4" is meaningless, you must have the full room number.
Also, some guest room numbers may have letters in them.
And meeting rooms at hotels are traditionally named, such as "Apple Blossom" or "Jefferson A", "Jefferson B", etc..
Simple. It doesn't. That would be a part of the description for the room.
But it's not a "description". The actual room number is a name. Remember hotel meeting rooms? They almost always have names rather than numbers.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply