CODEWITHSUNDEEP
mysqldate
Ramaraju.d
Ramaraju.d

Reputation: 1353

How to obtaining remaining available days in a month for booking?

I have a table for slots that have the following Schema

SlotId
FromDate
ToDate

I want to get the remaining days in that given month available for slot booking.

To be clear, I am trying to retrieve all the dates apart from the dates stored in the database(as those are already booked) for a given month.

For example, if a record have FromDate is equal to 2014-04-02 and ToDate is equal to 2014-04-06 I am expecting the following result:

2014-04-01
2014-04-07
...
2014-04-30

Although i am scripting in PHP, I am little curious about the query to accomplish this.

Upvotes: 1

Views: 172

Answers (3)

John Ruddell
John Ruddell

Reputation: 25842

So it is not an easy thing to do in mysql but here is something that should work. this gets the dates in any given month that are not booked... see fiddle for working example 

SELECT *, union_month.day_date
FROM (
    SELECT 1 AS day_date UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13 UNION ALL
    SELECT 14 UNION ALL
    SELECT 15 UNION ALL
    SELECT 16 UNION ALL
    SELECT 17 UNION ALL
    SELECT 18 UNION ALL
    SELECT 19 UNION ALL
    SELECT 20 UNION ALL
    SELECT 21 UNION ALL
    SELECT 22 UNION ALL
    SELECT 23 UNION ALL
    SELECT 24 UNION ALL
    SELECT 25 UNION ALL
    SELECT 26 UNION ALL
    SELECT 27 UNION ALL
    SELECT 28 UNION ALL
    SELECT 29 UNION ALL
    SELECT 30 UNION ALL
    SELECT 31
) AS union_month
LEFT JOIN myTable AS t ON union_month.day_date <> DAY(t.to_date) OR union_month.day_date <> DAY(t.from_date)
WHERE union_month.day_date <= DAY(LAST_DAY(t.to_date))
AND union_month.day_date NOT BETWEEN DAY(t.from_date) AND DAY(t.to_date)
GROUP BY union_month.day_date

for multiple dates in a month change the WHERE clause to this

WHERE
      union_month.day_date <= DAY(LAST_DAY(t.to_date))
      AND union_month.day_date not BETWEEN (select DAY(from_date) from myTable limit 0,1) AND (select DAY(to_date) from myTable limit 0,1)
      AND union_month.day_date not BETWEEN (select DAY(from_date) from myTable limit 1,1) AND (select DAY(to_date) from myTable limit 1,1)
      AND union_month.day_date not BETWEEN (select DAY(from_date) from myTable limit 2,1) AND (select DAY(to_date) from myTable limit 2,1)
GROUP BY union_month.day_date

working fiddle for multiple dates

Upvotes: 1

Shri
Shri

Reputation: 731

I have tried this. May it will help you. You need to use this query in loop. with some variables. In example I have consider April month only. You can do it for all months.

SELECT distinct * FROM (SELECT DATE_ADD('2014-04-01', INTERVAL t4+t16+t64+t256+t1024 DAY) freedays  FROM 
(SELECT 0 t4    UNION ALL SELECT 1   UNION ALL SELECT 2   UNION ALL SELECT 3  ) t4,
(SELECT 0 t16   UNION ALL SELECT 4   UNION ALL SELECT 8   UNION ALL SELECT 12 ) t16,   
(SELECT 0 t64   UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48 ) t64,      
(SELECT 0 t256  UNION ALL SELECT 64  UNION ALL SELECT 128 UNION ALL SELECT 192) t256,     
(SELECT 0 t1024 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768) t1024     
) b 
 WHERE freedays not between (select FrmDate from slotbooking limit 1) and (select ToDate from
  slotbooking limit 1) and freedays < '2014-04-30';

Upvotes: 0

Shri
Shri

Reputation: 731

You need remaining days count or dates which are free?

Upvotes: 0

Related Questions