CODEWITHSUNDEEP
mysql
Rikard
Rikard

Reputation: 7805

Add day from previous month to Select

I have a SELECT to give me the booked days of a apartment in a specific month:

SELECT db.the_date, db.id_state, db.id_client_booking, bs.class 
    FROM `day_bookings` as db 
    LEFT JOIN `bookings_states` as bs ON bs.id= db.id_state 
    WHERE id_item= 10
    AND YEAR(the_date)= 2016
    AND MONTH(the_date) = 6

How can I add the last day of the previous month to this query?

I thought about doing a new LEFT JOIN with the same query for month 5 but couldn't get it to work, syntax errors all the time.

I tried UNION ALL with 2 similar selects and got just the last one. I also tried nested SELECT, but it didn't pass over syntax errors.

Any suggestion on how to add the last month to this result?

Upvotes: 0

Views: 27

Answers (2)

Rikard
Rikard

Reputation: 7805

Managed to do it using

AND the_date BETWEEN DATE_SUB('2016-06-01', INTERVAL 1 DAY) AND LAST_DAY('2016-06-01')

this way it works for every month, despite 28, 30 or 31 length months.

So the end query is:

SELECT db.the_date, db.id_state, db.id_client_booking, db.id_item, bs.class 
    FROM `day_bookings` as db 
    LEFT JOIN `bookings_states` as bs ON bs.id= db.id_state 
    WHERE id_item= 10
    AND YEAR(the_date)= 2016
    AND the_date BETWEEN DATE_SUB('2016-06-01', INTERVAL 1 DAY) AND LAST_DAY('2016-06-01')

Upvotes: 0

minatverma
minatverma

Reputation: 1099

Try this :

SELECT db.the_date, db.id_state, db.id_client_booking, bs.class 
    FROM `day_bookings` as db 
    LEFT JOIN `bookings_states` as bs ON bs.id= db.id_state 
    WHERE id_item= 10
    AND YEAR(the_date)= 2016
    AND (MONTH(the_date) = 6 OR (MONTH(the_date) = 5 AND DAY(the_date) = 31))
    ;

OR clause is what you need.

Upvotes: 1

Related Questions