CODEWITHSUNDEEP
phpmysql
Deepanshu Jaiswal
Deepanshu Jaiswal

Reputation: 43

calculate price between given dates

Hotel_id   Room_id   Room_type   Start_date   End_date     Price
----------------------------------------------------------------
   13        2        standard   2012-08-01   2012-08-15   7000
   13        2        standard   2012-08-16   2012-08-31   7500
   13        2        standard   2012-09-01   2012-09-30   6000
   13        3         luxury    2012-08-01   2012-08-15   9000
   13        3         luxury    2012-08-16   2012-08-31   10000
   13        3         luxury    2012-09-01   2012-09-30   9500

Hi this is the structure and data of my table.

I need to create a mysql query for hotel booking, that would match in database user entered data:

  1. Date when they want to checkin and checkout
  2. Room type

For Ex:

If user selects Hotel with luxury room based on these dates (2012-08-30 to 2012-09-04) the total cost would be (10000*2) for 30th and 31st Aug + (9500*3) for 1st,2nd and 3rd Sep(4th checkout day don't include) that means total price will be 20000+28500=48500

So query should filter total price based on the Hotel_id,Room_id,Start_date,End_date and Price

Thanks

Upvotes: 1

Views: 2816

Answers (4)

Sedat Akgöz
Sedat Akgöz

Reputation: 1

A little addition to @eggyal solution so that if there are more than two month entries, some entries are not ignored; This change includes rows that were not checked with between, for example in a 90-day period, into the total. As an example, in the table given in the question, when the range 2012-08-01-2012-09-01 is queried, the price line dated 2012-08-16 2012-08-31 does not meet the between conditions, so it is not included in the total. This small appendix will add these lines to the total. Although quite old, the answers given here helped me a lot. Thanks everyone for the solutions.

    SELECT Room_id, 
SUM(price * (1 + DATEDIFF( LEAST(End_date, '2023-09-23' - INTERVAL 1 DAY), GREATEST(Start_date, '2023-06-25') ))) AS Total 
FROM mytable 
WHERE Room_type = 'luxury' 
AND (( '2023-09-23' - INTERVAL 1 DAY BETWEEN Start_date AND End_date) 
OR ('2023-06-25' BETWEEN Start_date AND End_date ) 
OR (End_date < '2023-09-23' AND Start_date > '2023-06-25')) GROUP BY Room_id

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

Use this solution:

SELECT     SUM(
               CASE WHEN a.Start_date = b.min_sd AND a.Start_date <> b.max_sd THEN
                        (DATEDIFF(a.End_date, '2012-08-30')+1) * a.Price
                    WHEN a.Start_date = b.max_sd AND a.Start_date <> b.min_sd THEN
                        DATEDIFF('2012-09-04', a.Start_date) * a.Price
                    WHEN (a.Start_date,a.Start_date) IN ((b.min_sd,b.max_sd)) THEN
                        (DATEDIFF('2012-09-04', '2012-08-30')+1) * a.Price
                    WHEN a.Start_date NOT IN (b.min_sd, b.max_sd)             THEN
                        (DATEDIFF(a.End_date, a.Start_date)+1) * a.Price
               END 
           ) AS totalprice
FROM       rooms a
CROSS JOIN (
           SELECT MIN(Start_date) AS min_sd,
                  MAX(Start_date) AS max_sd
           FROM   rooms
           WHERE  Room_type   = 'luxury'     AND
                  End_date   >= '2012-08-30' AND
                  Start_date <= '2012-09-04'
           ) b
WHERE      a.Room_type   = 'luxury'     AND
           a.End_date   >= '2012-08-30' AND
           a.Start_date <= '2012-09-04'

Replace occurances of 2012-08-30 and 2012-09-04 with your input start and end dates respectively.

This will account for start and end dates being in the same month as well as spanning across multiple months.

SQLFiddle Demo

Upvotes: 4

eggyal
eggyal

Reputation: 125865

You can use MySQL's BETWEEN ... AND ... operator to find the date ranges in which the desired booking falls (remember to take one day off of the given checkout date as, like you say, there is no night's stay), then group the results by room and take the SUM() of price times number of nights (which can be calculated using MySQL's LEAST() and GREATEST() functions):

SELECT   Room_id,
         SUM(Price * (1 + DATEDIFF(
              LEAST(End_date,   '2012-09-04' - INTERVAL 1 DAY),
           GREATEST(Start_date, '2012-08-30')
         ))) AS Total
FROM     mytable
WHERE    Room_type = 'luxury' AND (
              '2012-09-04' - INTERVAL 1 DAY
                           BETWEEN Start_date AND End_date
           OR '2012-08-30' BETWEEN Start_date AND End_date
         )
GROUP BY Room_id

See it on sqlfidde.

Upvotes: 2

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

set @Hotel_id :=13;
set @Room_id :=3;
set @Start_date :='2012-08-30'  ;
set @End_date :='2012-09-04';

 select sum(b.TotalPrice-b.deductions) as total_cost from
( select a.Price,a.StartDate,a.EndDate,price*(DATEDIFF(a.EndDate,a.StartDate)+1) as TotalPrice  
 ,case when a.EndDate=@End_date then a.Price else 0 end as deductions
 from
(select price,case when @Start_date>=Start_date then  @Start_date else Start_date end as StartDate
,case when @End_date<=End_date then  @End_date else End_date end as EndDate
 from h_booking h1 
where  Hotel_id=@Hotel_id
and Room_id=@Room_id
and (@Start_date between Start_date and End_date or @End_date between Start_date and End_date ))a )b

Upvotes: 0

Related Questions