Query with recursive subquery does not work

[Julio Herrera]

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.

 

[SkipVought]

Hi,

What does the field, Week_of_sale, mean?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Julio Herrera]

Hi SkipVought,-

Week_of_sale means the week of the year the reservation was sold, covering 2 years in advance. For example, a stay for 2020-11-14 (yyyy-mm-dd) sold on 2019-10-10 will have Week_of_sale = 41. Same stay sold on 2020-05-05 will have Week_of_sale = 70. That is why Week_of_sale ranges from 0 to 106 weeks (2 years).

Thak you for any assistance.

 

[SkipVought]

week-of-sale is really a misnomer, as it is in actuality, weeks-purchased-in-advance. Okay that solves one conundrum.

You are actually equivocating with respect to week-of-sale. How can WOS represent up to two years (in weeks) in advance AND ALSO in your example you are using an actual week of the year and not weeks in advance???
[pre]
Sold On Res Date Weeks in Advance Week-of-the-year of Sale
10/10/19 11/14/20 57.28571429 40.28571429
[/pre]

Second question: what is your query attempting to do? What information are you trying to get?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

sold on 2019-10-10 will have Week_of_sale = 41" - or is it week 40?

Based on code:

Dim dat1 As Date
dat1 = CDate("2019-10-10")
Debug.Print DateDiff("w", DateSerial(Year(dat1), 1, 1), dat1)

"sold on 2020-05-05 will have Week_of_sale = 70" - or is it week (17 + 52) = 69 ?
My years have 52 weeks

So, as long as you have Sold_On date, you do not need to keep Week_of_sale, you can always calculate it.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

For example, a stay for 2020-11-14 (yyyy-mm-dd) sold on 2019-10-10 will have Week_of_sale = 41. Same stay sold on 2020-05-05 will have Week_of_sale = 70.

Makes no sense!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

I think this may be what you're looking for...

[pre]
Reservation_line
Date_of_stay Week_of_sale Number_of_rooms
10/10/19 8.3 4
5/5/20 4.2 1
9/5/20 4.9 2
5/5/20 4.8 1
10/10/19 8.8 5
[/pre]

[pre]
Stay-date_week (I only included 10 weeks rather than 107)
Week_of_sale
0.00
1.00
2.00
3.00
4.00
5.00
6.00
7.00
8.00
9.00
10.00
[/pre]

The SQL...

SELECT  
  r.Date_of_stay
, s.Week_of_sale AS WOS
, SUM(r.Number_of_rooms) AS ROOMS
FROM Stay-date_week s 
LEFT OUTER JOIN Reservation_line r 
ON s.Week_of_sale = INT(r.Week_of_sale)
GROUP BY
  r.Date_of_stay
, s.Week_of_sale;

[pre]
Result
Date_of_stay WOS ROOMS
0
1
2
3
5/5/20 4 2
9/5/20 4 2
5
6
7
10/10/19 8 9
9
10

[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein