how to get rate for the next days (1 Viewer)

hatmak

Registered User.
Local time
Today, 03:55
Joined
Jan 17, 2015
Messages
121
i have table Reservation_tb

which I select gest ,hotel name and select period from date and to date


how I get single room rate from rates which in Hotel_RATE_tb which different from date to date

I need query to get this rate for ( if gest will stay in hotel three days )


eva from 02/02/2019 to 05/02/2019 in JWM Hotel

lookup in table Hotel_RATE_tb

first day rate will be 500
second day will be 500
third date will be 450

thanks in advanced for help
 

Attachments

  • HOTEL.zip
    21.3 KB · Views: 75

Dreamweaver

Well-known member
Local time
Today, 10:55
Joined
Nov 28, 2005
Messages
2,466
The folder is empty but if your rates table has a rate for a day of the week you can use code to check this and sum the total for the period of the guests stay
 

hatmak

Registered User.
Local time
Today, 03:55
Joined
Jan 17, 2015
Messages
121
file attached again

thanks MickJav for reply
but any hotel have more than rate in dates

please check database
 

Attachments

  • archive.zip
    32.9 KB · Views: 89

jdraw

Super Moderator
Staff member
Local time
Today, 06:55
Joined
Jan 23, 2006
Messages
15,361
hatmak,

I adjusted your query1 to the following which should help with your processing.
Code:
SELECT Reservation_tb.gest
    ,Reservation_tb.Hotel_
    ,Reservation_tb.DATE_FROM
    ,Reservation_tb.Date_to
    ,single_rate
    ,Hotel_RATE_tb.from_date
    ,Hotel_RATE_tb.to_date
FROM (
    Hotel_tb INNER JOIN Hotel_RATE_tb
        ON Hotel_tb.ID = Hotel_RATE_tb.Hotel
    )
INNER JOIN Reservation_tb
    ON Hotel_tb.ID = Reservation_tb.Hotel_
ORDER BY Hotel_RATE_tb.from_date;

You have all the field you need to do some calculations in the query.
Hope it' helpful.

PS You should not use lookups at the table field level.
 

ebs17

Well-known member
Local time
Today, 11:55
Joined
Feb 7, 2020
Messages
1,879
In addition, create a calendar table that contains a continuous date of a sufficient period of time.
With such an auxiliary table, periods can be broken down into individual days, thus bringing the reservation days together with the daily prices.
SQL:
SELECT
   R.gest,
   DR.Hotel,
   DR.Single_rate,
   DR.calDay
FROM
   Reservation_tb AS R
      INNER JOIN
         (
            SELECT
               HR.Hotel,
               HR.Single_rate,
               C.calDay
            FROM
               tblCalendar AS C,
               Hotel_RATE_tb AS HR
            WHERE
               C.calDay BETWEEN HR.from_date
                  AND
               HR.to_date
         ) AS DR
         ON R.Hotel_ = DR.Hotel
WHERE
   DR.calDay BETWEEN R.DATE_FROM
      AND
   R.Date_to - 1

Eberhard
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:55
Joined
Feb 19, 2013
Messages
16,553
another solution - you need to create a table of numbers (I called it tblNums) and populate a single numeric field (which I called num) with 0, 1, 2 etc up to a number that is the maximum number of days someone will stay. If you have stays in the hundreds of days, then there is a query I can provide that will do this for you.

SQL:
SELECT
     Reservation_tb.gest, 
     Reservation_tb.Hotel_, 
     Reservation_tb.DATE_FROM, 
     Reservation_tb.Date_to, 
     [Date_From]+[num] AS StayDate,
     Hotel_RATE_tb.Single_rate
FROM
     tblNums, 
     (Hotel_tb INNER JOIN Hotel_RATE_tb ON Hotel_tb.ID = Hotel_RATE_tb.Hotel) INNER JOIN Reservation_tb ON Hotel_tb.ID = Reservation_tb.Hotel_
WHERE
     (((tblNums.num)<=DateDiff("d",[DATE_FROM],[Date_to])) AND
     (([Date_From]+[num]) Between [from_date] And [to_date]))

this code produces this result
Query1

gestHotel_DATE_FROMDate_toStayDateSingle_rate
EvaJWM
02/02/2019​
05/02/2019​
02/02/2019​
500​
EvaJWM
02/02/2019​
05/02/2019​
03/02/2019​
500​
EvaJWM
02/02/2019​
05/02/2019​
04/02/2019​
450​
EvaJWM
02/02/2019​
05/02/2019​
05/02/2019​
450​
with regards to rates table - you need to be careful you don't repeat a date (i.e. from_date=to_date) or miss a day. You might want a 'check query to make sure this doesn't happen.
 

hatmak

Registered User.
Local time
Today, 03:55
Joined
Jan 17, 2015
Messages
121
In addition, create a calendar table that contains a continuous date of a sufficient period of time.
With such an auxiliary table, periods can be broken down into individual days, thus bringing the reservation days together with the daily prices.
SQL:
SELECT
   R.gest,
   DR.Hotel,
   DR.Single_rate,
   DR.calDay
FROM
   Reservation_tb AS R
      INNER JOIN
         (
            SELECT
               HR.Hotel,
               HR.Single_rate,
               C.calDay
            FROM
               tblCalendar AS C,
               Hotel_RATE_tb AS HR
            WHERE
               C.calDay BETWEEN HR.from_date
                  AND
               HR.to_date
         ) AS DR
         ON R.Hotel_ = DR.Hotel
WHERE
   DR.calDay BETWEEN R.DATE_FROM
      AND
   R.Date_to - 1

Eberhard

many thank for help

could you please explain calDay which are in DR QUERY
 

hatmak

Registered User.
Local time
Today, 03:55
Joined
Jan 17, 2015
Messages
121
hatmak,

I adjusted your query1 to the following which should help with your processing.
Code:
SELECT Reservation_tb.gest
    ,Reservation_tb.Hotel_
    ,Reservation_tb.DATE_FROM
    ,Reservation_tb.Date_to
    ,single_rate
    ,Hotel_RATE_tb.from_date
    ,Hotel_RATE_tb.to_date
FROM (
    Hotel_tb INNER JOIN Hotel_RATE_tb
        ON Hotel_tb.ID = Hotel_RATE_tb.Hotel
    )
INNER JOIN Reservation_tb
    ON Hotel_tb.ID = Reservation_tb.Hotel_
ORDER BY Hotel_RATE_tb.from_date;

You have all the field you need to do some calculations in the query.
Hope it' helpful.

PS You should not use lookups at the table field level.

Thanks for your help

could you please help in get no. of days for rate period to get total amount for stay in hotel
 

ebs17

Well-known member
Local time
Today, 11:55
Joined
Feb 7, 2020
Messages
1,879
explain calDay which are in DR QUERY
See the table definition according to the following DDL instructions.
SQL:
CREATE TABLE tblCalendar(calDay DATETIME);

CREATE UNIQUE INDEX [PrimaryKey] ON tblCalendar(calDay) WITH PRIMARY DISALLOW NULL;
After additional filling, calDay contains at least the values from 2/1/2019 to 2/15/2019, i.e. sufficient for the periods used from the other tables.

If you use fields with indexing directly from tables for comparisons (JOIN, WHERE) - directly means without using an additional calculation on these fields - you can also use existing indices and do something good for performance.
 

hatmak

Registered User.
Local time
Today, 03:55
Joined
Jan 17, 2015
Messages
121
another solution - you need to create a table of numbers (I called it tblNums) and populate a single numeric field (which I called num) with 0, 1, 2 etc up to a number that is the maximum number of days someone will stay. If you have stays in the hundreds of days, then there is a query I can provide that will do this for you.

SQL:
SELECT
     Reservation_tb.gest,
     Reservation_tb.Hotel_,
     Reservation_tb.DATE_FROM,
     Reservation_tb.Date_to,
     [Date_From]+[num] AS StayDate,
     Hotel_RATE_tb.Single_rate
FROM
     tblNums,
     (Hotel_tb INNER JOIN Hotel_RATE_tb ON Hotel_tb.ID = Hotel_RATE_tb.Hotel) INNER JOIN Reservation_tb ON Hotel_tb.ID = Reservation_tb.Hotel_
WHERE
     (((tblNums.num)<=DateDiff("d",[DATE_FROM],[Date_to])) AND
     (([Date_From]+[num]) Between [from_date] And [to_date]))

this code produces this result
Query1

gestHotel_DATE_FROMDate_toStayDateSingle_rate
EvaJWM
02/02/2019​
05/02/2019​
02/02/2019​
500​
EvaJWM
02/02/2019​
05/02/2019​
03/02/2019​
500​
EvaJWM
02/02/2019​
05/02/2019​
04/02/2019​
450​
EvaJWM
02/02/2019​
05/02/2019​
05/02/2019​
450​
with regards to rates table - you need to be careful you don't repeat a date (i.e. from_date=to_date) or miss a day. You might want a 'check query to make sure this doesn't happen.


thanks for your help

please last day 05/02/2019 is consider last day ( leave day)
we didn't paid this day
 

hatmak

Registered User.
Local time
Today, 03:55
Joined
Jan 17, 2015
Messages
121
See the table definition according to the following DDL instructions.
SQL:
CREATE TABLE tblCalendar(calDay DATETIME);

CREATE UNIQUE INDEX [PrimaryKey] ON tblCalendar(calDay) WITH PRIMARY DISALLOW NULL;
After additional filling, calDay contains at least the values from 2/1/2019 to 2/15/2019, i.e. sufficient for the periods used from the other tables.

If you use fields with indexing directly from tables for comparisons (JOIN, WHERE) - directly means without using an additional calculation on these fields - you can also use existing indices and do something good for performance.
could you please send example to explain it I can't do it correctly

regards
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:55
Joined
Feb 19, 2013
Messages
16,553
please last day 05/02/2019 is consider last day ( leave day)
take out the = in this part of the query

(((tblNums.num)<=DateDiff("d",[DATE_FROM],[Date_to])) AND
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:55
Joined
Feb 19, 2013
Messages
16,553
replace with nothing - it becomes

(((tblNums.num)<DateDiff("d",[DATE_FROM],[Date_to])) AND
 

ebs17

Well-known member
Local time
Today, 11:55
Joined
Feb 7, 2020
Messages
1,879
Your own initiative is very clear.

Eberhard
 

Attachments

  • HOTEL.zip
    25.2 KB · Views: 76

Users who are viewing this thread

Top Bottom