August 29, 2018 at 2:07 am
Hi guys,
let me try to explain the problem. There is a hotel with rooms for 2, 3 and 4 persons. Every room has a stock, as you can see in the list below:
ROOM_TYPE_CODE NUMBER_OF_PERSONS Stock
2PKG22SP 2 1
2PKD22 2 1
3PK233 3 1
3PK333ST 3 1
SUIA44BL 4 1
2PKA22SP 2 12
2PKO22-F 2 20
There is a max number of persons in a group that can book the hotel, 20. So, for this example, it's possible to book rooms for 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 and 20 persons. If the stock for the rooms for 3 persons is 0, then the possible combination is 2, 4, 6, 8, 10, 12, 14, 16, 18 and 20, meaning that, if a group of 5 persons want to book this hotel, they won't be able to do it.
Question is: how can I get the combination (based on stock, of course)?
Thanks in advance.
Mauricio
P.S: I really hope you can understand the question, feel free to ask me if you need more information.
August 29, 2018 at 4:17 am
Mauricio_ - Wednesday, August 29, 2018 2:07 AMHi guys,
let me try to explain the problem. There is a hotel with rooms for 2, 3 and 4 persons. Every room has a stock, as you can see in the list below:
ROOM_TYPE_CODE NUMBER_OF_PERSONS Stock
2PKG22SP 2 1
2PKD22 2 1
3PK233 3 1
3PK333ST 3 1
SUIA44BL 4 1
2PKA22SP 2 12
2PKO22-F 2 20
There is a max number of persons in a group that can book the hotel, 20. So, for this example, it's possible to book rooms for 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 and 20 persons. If the stock for the rooms for 3 persons is 0, then the possible combination is 2, 4, 6, 8, 10, 12, 14, 16, 18 and 20, meaning that, if a group of 5 persons want to book this hotel, they won't be able to do it.
Question is: how can I get the combination (based on stock, of course)?
Thanks in advance.Mauricio
P.S: I really hope you can understand the question, feel free to ask me if you need more information.
If there were 5 people couldn't they book a room for 4 and a room for 2 and just put 3 people in the room for 4 and 2 in the room for 2?
August 29, 2018 at 4:29 am
Hi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best. Here is it:
DROP TABLE IF EXISTS #NUMBERS
CREATE TABLE #NUMBERS
(
N INT NOT NULL
)
;WITH e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
INSERT INTO #NUMBERS
(
N
)
SELECT ROW_NUMBER() OVER (ORDER BY n) FROM e3 ORDER BY n;
DROP TABLE IF EXISTS #ROOMS
CREATE TABLE #ROOMS (
ROOM_ID INT NOT NULL,
PERSONS TINYINT NOT NULL,
STOCK TINYINT NOT NULL
)
INSERT INTO #ROOMS
(
ROOM_ID,
PERSONS,
STOCK
)
VALUES
( 1, -- ROOM_ID - int
2, -- PERSONS - tinyint
13 -- STOCK - tinyint
),
(2,
3,
0),
(3,
4,
2),
(4,7,1)
SELECT * FROM #ROOMS
DROP TABLE IF EXISTS #TEMPROOMS
CREATE TABLE #TEMPROOMS
( ID INT IDENTITY (1,1) NOT NULL,
PERSONS TINYINT NOT NULL,
TOTAL_PERSONS TINYINT NOT NULL
)
INSERT INTO #TEMPROOMS
(
PERSONS,
TOTAL_PERSONS
)
SELECT R.PERSONS, R.PERSONS * NUMBERS.N
FROM #ROOMS R
CROSS APPLY (SELECT N FROM #NUMBERS WHERE N <= R.STOCK) NUMBERS(N)
WHERE 1 = 1
AND R.STOCK > 0
AND R.PERSONS * NUMBERS.N <= 20
SELECT * FROM #TEMPROOMS
;WITH CTE AS
(SELECT ID, PERSONS, TOTAL_PERSONS
FROM #TEMPROOMS
UNION ALL
SELECT R2.ID,
R2.PERSONS,
CTE.TOTAL_PERSONS + (R2.TOTAL_PERSONS)
FROM #TEMPROOMS R2
CROSS JOIN CTE
WHERE 1 = 1
AND R2.ID > CTE.ID
AND R2.PERSONS <> CTE.PERSONS
)
SELECT DISTINCT CTE.TOTAL_PERSONS
FROM CTE
WHERE 1 = 1
AND CTE.TOTAL_PERSONS <= 20
ORDER BY CTE.TOTAL_PERSONS
August 29, 2018 at 7:17 am
Mauricio_ - Wednesday, August 29, 2018 4:29 AMHi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best.
So basically, this is homework or a theoretical challenge?
August 29, 2018 at 7:24 am
It's something I have to develop and I'm asking for another points of view.
August 29, 2018 at 7:33 am
Mauricio_ - Wednesday, August 29, 2018 4:29 AMHi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best. Here is it:
Then your scenario is not real-world at all. No hotel in it's right mind is going to insist that a given room be occupied by it's exact capacity whenever it's occupied at all. Which makes this problem either homework or something you found interesting on the internet. I do applaud you for providing your solution, but as you have a solution for something that isn't real world, there may not be many folks ready to expend much effort on it. You kind of have to ask "What's in it for them?" Anything they learn is not necessarily linkable to real-world situations, so it's in the category of a solution for something that's never going to happen.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 29, 2018 at 7:35 am
Mauricio_ - Wednesday, August 29, 2018 7:24 AMIt's something I have to develop and I'm asking for another points of view.
Have you tried your solution on a large amount of data?
If so, you could paste DDL and the the inserts into the thread and I'm sure you will get lots of people who will try to make it faster.
August 29, 2018 at 10:59 am
sgmunson - Wednesday, August 29, 2018 7:33 AMMauricio_ - Wednesday, August 29, 2018 4:29 AMHi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best. Here is it:Then your scenario is not real-world at all. No hotel in it's right mind is going to insist that a given room be occupied by it's exact capacity whenever it's occupied at all. Which makes this problem either homework or something you found interesting on the internet. I do applaud you for providing your solution, but as you have a solution for something that isn't real world, there may not be many folks ready to expend much effort on it. You kind of have to ask "What's in it for them?" Anything they learn is not necessarily linkable to real-world situations, so it's in the category of a solution for something that's never going to happen.
Hi,
you're wrong. The accommodations (hotels and rooms) are going to be booked for a music festival. The restriction about the exact number of people on each rooms was something that they want. I know, it's sound a bit weird because if I want to go to the festival alone and book a room for 2, I cannot. They expect a huge amount of people going there (there is accommodations for 30000 people) and they want everything booked. If they start allowing less people in rooms the result will be totally booked but with less participants so that restriction, in fact, exists. They are more interested in people spending money in the festival, not at hotels. The location where this festival will take place is small, in the Alps, and of course they want a big success.
I'm not trying to do my "homework" here. It's a real case I'm facing on, my solution could work but I didn't try yet with a big amount of data or as a part of a select and that's why I was asking for the best way to do it.
August 29, 2018 at 1:38 pm
I'm not sure there is a generalised ideal solution to the problem, but there probably some general rules you could apply. Any party with an odd number of members needs at least one room of three and then probably rooms allocated to satisfy their needs from the largest possible. And then all remaining rooms can be allocated largest to smallest to the biggest group that would fit.
Of course that's not a "fair" allocation, as groups of odd numbers will be prioritised as would larger groups. If allocation needs to be fair (such as on some variation of first-come first-serve basis) then you likely have to accept some degree of inefficiency in allocation.
August 30, 2018 at 10:32 am
Mauricio_ - Wednesday, August 29, 2018 10:59 AMsgmunson - Wednesday, August 29, 2018 7:33 AMMauricio_ - Wednesday, August 29, 2018 4:29 AMHi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best. Here is it:Then your scenario is not real-world at all. No hotel in it's right mind is going to insist that a given room be occupied by it's exact capacity whenever it's occupied at all. Which makes this problem either homework or something you found interesting on the internet. I do applaud you for providing your solution, but as you have a solution for something that isn't real world, there may not be many folks ready to expend much effort on it. You kind of have to ask "What's in it for them?" Anything they learn is not necessarily linkable to real-world situations, so it's in the category of a solution for something that's never going to happen.
Hi,
you're wrong. The accommodations (hotels and rooms) are going to be booked for a music festival. The restriction about the exact number of people on each rooms was something that they want. I know, it's sound a bit weird because if I want to go to the festival alone and book a room for 2, I cannot. They expect a huge amount of people going there (there is accommodations for 30000 people) and they want everything booked. If they start allowing less people in rooms the result will be totally booked but with less participants so that restriction, in fact, exists. They are more interested in people spending money in the festival, not at hotels. The location where this festival will take place is small, in the Alps, and of course they want a big success.
I'm not trying to do my "homework" here. It's a real case I'm facing on, my solution could work but I didn't try yet with a big amount of data or as a part of a select and that's why I was asking for the best way to do it.
Then the organizers are not terribly bright. You can't possibly expect to either 1) treat all parties trying to attend, fairly, or 2) actually fill those rooms with people, because someone somewhere will be willing to pay for the 2nd guest just to get a room. Therefore, the goal is actually going to be impossible to fully realize, and likely, not all that thoroughly. Additionally, you will probably actually HAVE LESS OVERALL ATTENDANCE, because individuals will effectively be discouraged from attending at all. Sounds particularly foolish to me. And I'll guess choice of venue is probably in a location that just doesn't actually have enough in the way of hotels to begin with. I don't waste time trying to solve those kinds of problems. The solutions are obvious - stay the heck out of that part of the travel arrangements and leave it to everyone attending to take care of themselves.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 30, 2018 at 10:44 am
sgmunson - Thursday, August 30, 2018 10:32 AMMauricio_ - Wednesday, August 29, 2018 10:59 AMsgmunson - Wednesday, August 29, 2018 7:33 AMMauricio_ - Wednesday, August 29, 2018 4:29 AMHi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best. Here is it:Then your scenario is not real-world at all. No hotel in it's right mind is going to insist that a given room be occupied by it's exact capacity whenever it's occupied at all. Which makes this problem either homework or something you found interesting on the internet. I do applaud you for providing your solution, but as you have a solution for something that isn't real world, there may not be many folks ready to expend much effort on it. You kind of have to ask "What's in it for them?" Anything they learn is not necessarily linkable to real-world situations, so it's in the category of a solution for something that's never going to happen.
Hi,
you're wrong. The accommodations (hotels and rooms) are going to be booked for a music festival. The restriction about the exact number of people on each rooms was something that they want. I know, it's sound a bit weird because if I want to go to the festival alone and book a room for 2, I cannot. They expect a huge amount of people going there (there is accommodations for 30000 people) and they want everything booked. If they start allowing less people in rooms the result will be totally booked but with less participants so that restriction, in fact, exists. They are more interested in people spending money in the festival, not at hotels. The location where this festival will take place is small, in the Alps, and of course they want a big success.
I'm not trying to do my "homework" here. It's a real case I'm facing on, my solution could work but I didn't try yet with a big amount of data or as a part of a select and that's why I was asking for the best way to do it.Then the organizers are not terribly bright. You can't possibly expect to either 1) treat all parties trying to attend, fairly, or 2) actually fill those rooms with people, because someone somewhere will be willing to pay for the 2nd guest just to get a room. Therefore, the goal is actually going to be impossible to fully realize, and likely, not all that thoroughly. Additionally, you will probably actually HAVE LESS OVERALL ATTENDANCE, because individuals will effectively be discouraged from attending at all. Sounds particularly foolish to me. And I'll guess choice of venue is probably in a location that just doesn't actually have enough in the way of hotels to begin with. I don't waste time trying to solve those kinds of problems. The solutions are obvious - stay the heck out of that part of the travel arrangements and leave it to everyone attending to take care of themselves.
No, I'm sorry but you're wrong. This festival will be hold, for the first time, in winter. The summer edition is sucessfull with more than 200000 persons. For the winter edition, and because will be in the Alps, where there are small towns, only a maximum of 30000 persons will be allowed. Keep in mind that we're talking about a specific city, not a country. So they want everything full booked, they have rooms for 30000 persons and they don't want only 15000 individuals using a 2 persons room each one. You think this is stupid, however they expect to fully booked everything and a lot of people without a chance of going there (there is not enough place for cars, the closest airport is more than 1 hour away, etc.). Just to give you an idea, the web page is not open yet and they have already sold more than 4000 tickets/accommodations. Doesn't it sound logic for you? I'm sorry, it does for them and I must, as a developer, to create a solution.
Beside all this explanation, with just a few adjustment, my idea I posted yesterday works fine. I know it's not the best and can be improved but I'm getting what I want in 2 seconds and that's enough for all hotels. Passing the hotel ID as parameter, the output takes a few milliseconds.
I appreciate everybody help and, once again, I'm sorry if you think this was some kind of homework or exercise. I was trying to discuss a real problem, even if this doesn't like to everyone.
August 30, 2018 at 11:05 am
Mauricio_ - Thursday, August 30, 2018 10:44 AMsgmunson - Thursday, August 30, 2018 10:32 AMMauricio_ - Wednesday, August 29, 2018 10:59 AMsgmunson - Wednesday, August 29, 2018 7:33 AMMauricio_ - Wednesday, August 29, 2018 4:29 AMHi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best. Here is it:Then your scenario is not real-world at all. No hotel in it's right mind is going to insist that a given room be occupied by it's exact capacity whenever it's occupied at all. Which makes this problem either homework or something you found interesting on the internet. I do applaud you for providing your solution, but as you have a solution for something that isn't real world, there may not be many folks ready to expend much effort on it. You kind of have to ask "What's in it for them?" Anything they learn is not necessarily linkable to real-world situations, so it's in the category of a solution for something that's never going to happen.
Hi,
you're wrong. The accommodations (hotels and rooms) are going to be booked for a music festival. The restriction about the exact number of people on each rooms was something that they want. I know, it's sound a bit weird because if I want to go to the festival alone and book a room for 2, I cannot. They expect a huge amount of people going there (there is accommodations for 30000 people) and they want everything booked. If they start allowing less people in rooms the result will be totally booked but with less participants so that restriction, in fact, exists. They are more interested in people spending money in the festival, not at hotels. The location where this festival will take place is small, in the Alps, and of course they want a big success.
I'm not trying to do my "homework" here. It's a real case I'm facing on, my solution could work but I didn't try yet with a big amount of data or as a part of a select and that's why I was asking for the best way to do it.Then the organizers are not terribly bright. You can't possibly expect to either 1) treat all parties trying to attend, fairly, or 2) actually fill those rooms with people, because someone somewhere will be willing to pay for the 2nd guest just to get a room. Therefore, the goal is actually going to be impossible to fully realize, and likely, not all that thoroughly. Additionally, you will probably actually HAVE LESS OVERALL ATTENDANCE, because individuals will effectively be discouraged from attending at all. Sounds particularly foolish to me. And I'll guess choice of venue is probably in a location that just doesn't actually have enough in the way of hotels to begin with. I don't waste time trying to solve those kinds of problems. The solutions are obvious - stay the heck out of that part of the travel arrangements and leave it to everyone attending to take care of themselves.
No, I'm sorry but you're wrong. This festival will be hold, for the first time, in winter. The summer edition is sucessfull with more than 200000 persons. For the winter edition, and because will be in the Alps, where there are small towns, only a maximum of 30000 persons will be allowed. Keep in mind that we're talking about a specific city, not a country. So they want everything full booked, they have rooms for 30000 persons and they don't want only 15000 individuals using a 2 persons room each one. You think this is stupid, however they expect to fully booked everything and a lot of people without a chance of going there (there is not enough place for cars, the closest airport is more than 1 hour away, etc.). Just to give you an idea, the web page is not open yet and they have already sold more than 4000 tickets/accommodations. Doesn't it sound logic for you? I'm sorry, it does for them and I must, as a developer, to create a solution.
Beside all this explanation, with just a few adjustment, my idea I posted yesterday works fine. I know it's not the best and can be improved but I'm getting what I want in 2 seconds and that's enough for all hotels. Passing the hotel ID as parameter, the output takes a few milliseconds.
I appreciate everybody help and, once again, I'm sorry if you think this was some kind of homework or exercise. I was trying to discuss a real problem, even if this doesn't like to everyone.
Excuse me, but a small town in the Alps is not an "appropriate venue" for a widely popular music festival that is capable of attracting 200,000 people, under ANY circumstances, for precisely all the reasons you want to book rooms efficiently. And before we waste any more time on that, I'm bowing out, as I refuse to debate facts. It never does anyone any good.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 30, 2018 at 11:21 am
sgmunson - Thursday, August 30, 2018 11:05 AMMauricio_ - Thursday, August 30, 2018 10:44 AMsgmunson - Thursday, August 30, 2018 10:32 AMMauricio_ - Wednesday, August 29, 2018 10:59 AMsgmunson - Wednesday, August 29, 2018 7:33 AMMauricio_ - Wednesday, August 29, 2018 4:29 AMHi,
no, it's not possible, the room must have the exact number of persons that allows.I've got a solution, not sure if it's the best. Here is it:Then your scenario is not real-world at all. No hotel in it's right mind is going to insist that a given room be occupied by it's exact capacity whenever it's occupied at all. Which makes this problem either homework or something you found interesting on the internet. I do applaud you for providing your solution, but as you have a solution for something that isn't real world, there may not be many folks ready to expend much effort on it. You kind of have to ask "What's in it for them?" Anything they learn is not necessarily linkable to real-world situations, so it's in the category of a solution for something that's never going to happen.
Hi,
you're wrong. The accommodations (hotels and rooms) are going to be booked for a music festival. The restriction about the exact number of people on each rooms was something that they want. I know, it's sound a bit weird because if I want to go to the festival alone and book a room for 2, I cannot. They expect a huge amount of people going there (there is accommodations for 30000 people) and they want everything booked. If they start allowing less people in rooms the result will be totally booked but with less participants so that restriction, in fact, exists. They are more interested in people spending money in the festival, not at hotels. The location where this festival will take place is small, in the Alps, and of course they want a big success.
I'm not trying to do my "homework" here. It's a real case I'm facing on, my solution could work but I didn't try yet with a big amount of data or as a part of a select and that's why I was asking for the best way to do it.Then the organizers are not terribly bright. You can't possibly expect to either 1) treat all parties trying to attend, fairly, or 2) actually fill those rooms with people, because someone somewhere will be willing to pay for the 2nd guest just to get a room. Therefore, the goal is actually going to be impossible to fully realize, and likely, not all that thoroughly. Additionally, you will probably actually HAVE LESS OVERALL ATTENDANCE, because individuals will effectively be discouraged from attending at all. Sounds particularly foolish to me. And I'll guess choice of venue is probably in a location that just doesn't actually have enough in the way of hotels to begin with. I don't waste time trying to solve those kinds of problems. The solutions are obvious - stay the heck out of that part of the travel arrangements and leave it to everyone attending to take care of themselves.
No, I'm sorry but you're wrong. This festival will be hold, for the first time, in winter. The summer edition is sucessfull with more than 200000 persons. For the winter edition, and because will be in the Alps, where there are small towns, only a maximum of 30000 persons will be allowed. Keep in mind that we're talking about a specific city, not a country. So they want everything full booked, they have rooms for 30000 persons and they don't want only 15000 individuals using a 2 persons room each one. You think this is stupid, however they expect to fully booked everything and a lot of people without a chance of going there (there is not enough place for cars, the closest airport is more than 1 hour away, etc.). Just to give you an idea, the web page is not open yet and they have already sold more than 4000 tickets/accommodations. Doesn't it sound logic for you? I'm sorry, it does for them and I must, as a developer, to create a solution.
Beside all this explanation, with just a few adjustment, my idea I posted yesterday works fine. I know it's not the best and can be improved but I'm getting what I want in 2 seconds and that's enough for all hotels. Passing the hotel ID as parameter, the output takes a few milliseconds.
I appreciate everybody help and, once again, I'm sorry if you think this was some kind of homework or exercise. I was trying to discuss a real problem, even if this doesn't like to everyone.Excuse me, but a small town in the Alps is not an "appropriate venue" for a widely popular music festival that is capable of attracting 200,000 people, under ANY circumstances, for precisely all the reasons you want to book rooms efficiently. And before we waste any more time on that, I'm bowing out, as I refuse to debate facts. It never does anyone any good.
No, Steve, the winter edition is for 30000 people, not 200000 (that's the summer edition). Just to clarify, I'm not trying to discuss with you, only explain the problem.
August 31, 2018 at 2:02 am
This stuff happens all the time in the real world (which is now a prerequisite for receiving help on SSC, apparently!). For example I can't book 2 of 3 adjacent seats for popular shows in my city as it would leave a 1 person gap which is not allowed - they'd rather hold those seats for a group of 3 and make me sit somewhere else or go on a different date. It's very common for events that are oversubscribed (such as the Alps-fest in question).
August 31, 2018 at 3:40 am
funbi - Friday, August 31, 2018 2:02 AMThis stuff happens all the time in the real world (which is now a prerequisite for receiving help on SSC, apparently!). For example I can't book 2 of 3 adjacent seats for popular shows in my city as it would leave a 1 person gap which is not allowed - they'd rather hold those seats for a group of 3 and make me sit somewhere else or go on a different date. It's very common for events that are oversubscribed (such as the Alps-fest in question).
Thanks for understand my problem, funbi 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply