I hope none of are proposing using the meeting room name as a primary key on the rooms table.  They are always subject to change....

Doubt you will find many meeting...

If you wanted more information on why your original code doesn't work, put

PRINT @SQL

between your SET and EXEC.  You will see that the query is badly formatted...

• This reply was modified 2 years, 3 months ago by  aaron.reese.

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

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

doesn't work if there are more than 9 floors, or the ground floor does not have leading zeros (which it won't if it is an INT) or if basement floors...

Don't know. Not my system.  The point I was making (and has obviously been lost in the message - my fault) was the distinction that in this particular domain  Room...

I guess I'd have to disagree, Aaron

Doesn't happen often.  As for storing dates and times separately it depends on what you want to do with them: If you are running...

Ive been writing t-SQL code for 25 years and NEVER come across FIRST_VALUE, LAST_VALUE.  I have always written a RANK() OVER() or a MIN() OVER() in a CTE and then...

Agreed, which is why it should be stored as two separate fields.  Could do it on the table with a couple of computed columns.

So with my Joe Celko hat on 🙂

Is Room Number a numeric value or not?  Most people would say yes, but in fact I would say no.  The reason being...

I think you are probably looking for RANK() OVER(PARTITION BY  MBOL_LN ORDER BY  LD_JN) AS [SEQ_NO]

This will give you a sequential RANK value which resets each time MOBL_LN changes.   If...

So the wrong solution to the wrong problem,  What you actually need is a table (or tables) that ulimately hold the commission allocation details: SalesMan, Customer, Start, End  and link...

In response to both frederico_fonseca and ratbak

I am using global temp tables because I need to persist the data across SPIDs.  The table in question is built from some highly...

I miss Gail 🙁

I met her once.  I picked her up from Birmingham international airport when she came over for SQLBITS one year.  She is/was one of the most knowlegeble...

As helpful as ever Joe.  It would be lovely if we could all live in the world of perfect 3NF data with proper entity identifiers and considered data types, unfortunately...