CODEWITHSUNDEEP
mysqlbetween
user2660409
user2660409

Reputation: 85

Mysql query to retrieve record using between for two dates

I have one requirement and i am totally confused how to write the query. I am a fresher in this company so please help me

User will select the following Dates:

Ex: fromDate: 2013-08-02 toDate: 2013-09-03 (YYYY-MM-DD)

id  resort_id   room_id Date1       Date2       price
5   35      45          2013-11-01  2013-11-30  3000.00
6   35      50          2013-07-25  2013-08-25  2000.00
7   35      541         2013-07-25  2013-08-25  4000.00
8   35      541         2013-08-26  2013-09-26  4000.00

Now i should get price based result for each date or sum of the price for both the date group by room id

Expected result

id  resort_id   room_id     Date1       Date2       price
6   35      50              2013-07-25  2013-08-25  2000.00
7   35      541             2013-07-25  2013-08-25  4000.00
8   35      541             2013-08-26  2013-09-26  4000.00

OR

id  resort_id      room_id  price
6   35             50       2000.00
7   35             541      8000.00

Upvotes: 3

Views: 27191

Answers (6)

Raja Ram T
Raja Ram T

Reputation: 864

SELECT * FROM lr_price_peak_rates  WHERE  `from_date`>='2014-04-08' AND `to_date`<='2014-04-30'

try the above query it can be working fine

Upvotes: 3

user3057670
user3057670

Reputation: 3

SELECT * FROM rooms WHERE date between Date1 AND Date2 UNION SELECT * FROM rooms WHERE date BETWEEN Date1 AND Date2

Upvotes: 0

SoWhat
SoWhat

Reputation: 5622

Try this query

SELECT * FROM `rooms` WHERE "2013-08-02" between Date1 AND Date2 UNION 
SELECT * FROM `rooms` WHERE "2013-09-03" BETWEEN Date1 AND Date2

Its a single query. Spread it on two lines for easier readability. I am pretty sure this query is missing something. I'll need a larger test set to verify. This won't select the middle cases. Change the first date with your user's selected dates.

Upvotes: 2

zxc
zxc

Reputation: 1526

hey try this (sql server )

SELECT room_id,SUM(price) FROM @table WHERE date1>='07/25/2013' AND date2<='09/26/2013' GROUP BY room_id

Upvotes: 1

aizaz
aizaz

Reputation: 3062

Try this query

SELECT
  id,
  resort_id,
  room_id,
  Date1,
  Date2,
  SUM(price) AS Total_Price
FROM
  MyTable
WHERE Date1> '2013-02-08' AND Date2 < '2013-02-09'

Upvotes: 1

Raj Jagani
Raj Jagani

Reputation: 758

select * from table_name where date1 > '08/02/2013' AND date2 < '09/02/2013' ORDER BY date1 ASC, price ASC

Upvotes: 0

Related Questions