calculating for last week

Database

calculating for last week

Hi folks,

I am unable to resolve one issue. Please look at the query below.

Select
hotel_id,
hotel_week_id,
sum(paidearly_rooms_rev) as reserved_rooms_rev,
sum(paidlate_rooms_rev) as blocked_rooms_rev
from
hotprod.hotelsalestab
where
hotel_week_id = (select prior_hotel_week_id from hotel_calendar_tab)
group by 1, 2

I am trying to calculate the revenue gained for two columns in the last week.
So when ever the user selects a hotel_week_id like 2009091 he should get the rev for 2009084

we have this column that gives us the last week id from hotel_Calendar_tab
I want this to happen for every row.
But the query above says more than one value returned by subquery and does not execute.

For reserved_rooms_rev i should get last weeks revenue.

How do I resolve this?
Thanks in advance..

1 REPLY

Re: calculating for last week

Hi,
You will have to use the following query for this:
Hi folks,

I am unable to resolve one issue. Please look at the query below.

Select
hotel_id,
hotel_week_id,
sum(paidearly_rooms_rev) as reserved_rooms_rev,
sum(paidlate_rooms_rev) as blocked_rooms_rev
from
hotprod.hotelsalestab AS A
where
hotel_week_id = (select prior_hotel_week_id from hotel_calendar_tab WHERE hotel_week_id = A.hotel_week_id)
group by 1, 2

This derived table will pass the parameter of hotel_week_id for each iterative run in to the derived table (your SELECT statement) and validate the prior_week_id as a return value. This will return only one value back unlike the full select which will return all data from the hotel_valendar table. I don't have access to a system and hence haven't been able to run and validate the query. If you have questions or the query doesn't work, get in touch.