How do I query to exclude the "first position of a 3 digit number"

Question

How do I query to exclude the "first position of a 3 digit number"

September 6, 2022 at 6:49 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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

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)

aaron.reese SSChampion Points: 13498 More actions · Answer 2

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..

aaron.reese SSChampion Points: 13498 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

aaron.reese wrote:

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

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

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.

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

Jeff Moden wrote:

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

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 😉

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Steve Jones - SSC Editor wrote:

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

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

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

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 12

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

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.

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 14

Jeff Moden wrote:

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".