Mysql query to retrieve record using between for two dates

Question

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

Date1 and Date 2 similar to from_date and to_date. price should be shown in the result if the user provided date between date1 and date2 — user2660409
– user2660409, Commented Aug 7, 2013 at 11:04

Raja Ram T · Accepted Answer · 2014-04-02 07:04:35Z

3

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

answered Apr 2, 2014 at 7:04

Raja Ram T

8628 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SoWhat · Accepted Answer · 2013-08-07 11:43:04Z

2

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.

answered Aug 7, 2013 at 11:43

SoWhat

5,6222 gold badges30 silver badges64 bronze badges

3 Comments

user2660409 Over a year ago

@SomeshMukerjee This query works fine and i am getting first expected result.

user2660409 Over a year ago

Myself written one query and i am getting 2nd expected result.

SELECT resort_id, room_id, SUM( test_price.price )      FROM test_price     WHERE  '2013-08-02'     BETWEEN from_date     AND to_date     OR  '2013-09-03'     BETWEEN from_date     AND to_date     GROUP BY room_id

SoWhat Over a year ago

careful though this might not work if the date falls between january 2013 and October 2013. It won't select the rooms in between, but you should be able to build from here

aizaz · Accepted Answer · 2013-08-07 11:08:31Z

1

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'

answered Aug 7, 2013 at 11:08

aizaz

3,0729 gold badges28 silver badges57 bronze badges

2 Comments

user2660409 Over a year ago

@SomeshMukherjee Thank you for your comment. Crescent Can you provide me interval based select query?

user2660409 Over a year ago

@SomeshMukherjee i have provided yyyy-mm-dd format. Thanks in advance

zxc · Accepted Answer · 2013-08-07 11:29:42Z

1

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

answered Aug 7, 2013 at 11:29

zxc

1,5263 gold badges13 silver badges35 bronze badges

1 Comment

user2660409 Over a year ago

This is working fine with your given date. But user may select any date range.

Raj Jagani · Accepted Answer · 2013-08-07 11:06:40Z

0

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

answered Aug 7, 2013 at 11:06

Raj Jagani

7333 gold badges6 silver badges21 bronze badges

1 Comment

user2660409 Over a year ago

user dates should be between date1 and date2. for ex: date: 08/02/2013 is applicable with rowid: 6 and 7, date: 09/02/2013 is applicable with row id 8. Using your query i am getting 0 results.

user3057670 · Accepted Answer · 2014-01-17 08:02:53Z

0

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

answered Jan 17, 2014 at 8:02

user3057670

31 silver badge4 bronze badges

Collectives™ on Stack Overflow

Mysql query to retrieve record using between for two dates

6 Answers 6

Comments

3 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

3 Comments

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related