Julio Herrera
Programmer
Hello,
I am a new member of the Forum, and I would be grateful if someone could help me out.
I have a table (Reservation_line) with 3 meaningful columns (for this question): Date_of_stay, Week_of_sale, Number_of_rooms. This table has data of hotel reservations for somes dates of two years and for some Week_of_sale ranging from 0 to 106.
I have a second table (Stay-date_week) with two columns: Date, Week_of_sale which range two years of dates (365 + 365 days) and 107 weeks = 78.110 records.
What I need to get is a table in which for each Date - Week_of_sale (no gaps) of the second table it sums up Number_of_rooms accumulatively from the first table.
Step by step it would be:
SELECT Reservation_line.Date_of_stay, 0 AS WeekOfSale, Sum(Reservation_line.Number_of_rooms) AS RN
FROM Reservation_line
WHERE Reservation_line.Week_of_sale <=0
GROUP BY Reservation_line.Date_of_stay
UNION SELECT Reservation_line.Date_of_stay, 1 AS WeekOfSale, Sum(Reservation_line.Number_of_rooms) AS RN
FROM Reservation_line
WHERE Reservation_line.Week_of_sale <=1
GROUP BY Reservation_line.Date_of_stay
UNION SELECT Reservation_line.Date_of_stay, 2 AS WeekOfSale, Sum(Reservation_line.Number_of_rooms) AS RN
FROM Reservation_line
WHERE Reservation_line.Week_of_sale <=2
GROUP BY Reservation_line.Date_of_stay;
Etc.
Since no gaps in week_of_sale is acceptable, I guess I need a LEFT JOIN between 'Stay-date_week' and 'Reservation_line' tables.
But I do not know how to resolve what seems to be a subquery. I have tried a bunch of times with no success.
Thank you very much for any assistance.
Regards.
I am a new member of the Forum, and I would be grateful if someone could help me out.
I have a table (Reservation_line) with 3 meaningful columns (for this question): Date_of_stay, Week_of_sale, Number_of_rooms. This table has data of hotel reservations for somes dates of two years and for some Week_of_sale ranging from 0 to 106.
I have a second table (Stay-date_week) with two columns: Date, Week_of_sale which range two years of dates (365 + 365 days) and 107 weeks = 78.110 records.
What I need to get is a table in which for each Date - Week_of_sale (no gaps) of the second table it sums up Number_of_rooms accumulatively from the first table.
Step by step it would be:
SELECT Reservation_line.Date_of_stay, 0 AS WeekOfSale, Sum(Reservation_line.Number_of_rooms) AS RN
FROM Reservation_line
WHERE Reservation_line.Week_of_sale <=0
GROUP BY Reservation_line.Date_of_stay
UNION SELECT Reservation_line.Date_of_stay, 1 AS WeekOfSale, Sum(Reservation_line.Number_of_rooms) AS RN
FROM Reservation_line
WHERE Reservation_line.Week_of_sale <=1
GROUP BY Reservation_line.Date_of_stay
UNION SELECT Reservation_line.Date_of_stay, 2 AS WeekOfSale, Sum(Reservation_line.Number_of_rooms) AS RN
FROM Reservation_line
WHERE Reservation_line.Week_of_sale <=2
GROUP BY Reservation_line.Date_of_stay;
Etc.
Since no gaps in week_of_sale is acceptable, I guess I need a LEFT JOIN between 'Stay-date_week' and 'Reservation_line' tables.
But I do not know how to resolve what seems to be a subquery. I have tried a bunch of times with no success.
Thank you very much for any assistance.
Regards.