CODEWITHSUNDEEP
mysqlsqldatewebcalendar
Flamur Mavraj
Flamur Mavraj

Reputation: 64

SQL/Mysql Query available dates in database

I need some help querying my calendar/dates table

Scenario: I have a "calendar" table with dates, user will set his available dates, usually day by day. So my table looks like this:

+------+------------+---------------------+---------------------+ 
| ID   | user_id    | start_date          | end_date            | 
+------+------------+---------------------+---------------------+ 
|    1 |          1 | 2016-09-01 08:00:00 | 2016-09-01 16:00:00 | 
|    2 |          1 | 2016-09-03 08:00:00 | 2016-09-03 16:00:00 | 
|    3 |          1 | 2016-09-04 08:00:00 | 2016-09-04 16:00:00 | 
|    3 |          1 | 2016-09-05 08:00:00 | 2016-09-05 16:00:00 | 
+------+------------+---------------------+---------------------+

This means user 1 is available on the 1st, 3rd, 4th and 5th.

Lets say I want to query the table and find if user is available from date 2016-09-01 08:00:00 to 2016-09-05 16:00:00, this query must return zero rows since the user is not available on the 2nd of September. But if query from date 2016-09-03 08:00:00 to 2016-09-05 16:00: 00 then it will return these 3 rows.

Hope someone can help me with this

Upvotes: 1

Views: 514

Answers (2)

1000111
1000111

Reputation: 13519

This could be one way (for a single user).

Note @endDate and @startDate are the supplied date fields to search.

SELECT 
*
FROM your_table 
WHERE EXISTS (
    SELECT 
    user_id
    FROM your_table 
    WHERE start_date >= @startDate 
    AND start_date <= @endDate
    AND user_id = 1
    GROUP BY user_id 
    HAVING SUM((DATEDIFF(end_date,start_date)+1)) = DATEDIFF(@endDate,@startDate)+1
)
AND start_date >= @startDate 
AND start_date <= @endDate
AND user_id = 1

Note:

If the supplied date range falls within any range bounded by start_date and end_date (exclusive) then it won't work.

Since SUM((DATEDIFF(end_date,start_date)+1)) = DATEDIFF(@endDate,@startDate)+1 won't be equal in this case. Condition

In this case, you need to stay within the required boundary. Here the boundary is demarcated by the smaller value of end_date and @endDate and the larger value of start_date and @startDate.

Suppose, you have the following record (only one)

start_date = 2016-09-01 and end_date=2016-09-05.

And @startDate=2016-09-02 , @endDate=2016-09-04

Now check the above condition will fail for this set of data.

In this case you need to adopt the following query:

SELECT 
*
FROM your_table 
WHERE EXISTS (
    SELECT 
    user_id
    FROM your_table 
    WHERE end_date >= @startDate 
    AND start_date <= @endDate
    AND user_id = 1
    GROUP BY user_id 
    HAVING SUM((DATEDIFF(LEAST(end_date,@endDate),GREATEST(start_date,@startDate))+1)) = DATEDIFF(@endDate,@startDate)+1
)
AND end_date >= @startDate 
AND start_date <= @endDate
AND user_id = 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Assuming the periods in the table are not overlapping, you can count the number of days. The days in the period are then:

select sum(datediff(least($period_end, end_date),
                    greatest($period_start, start_date)
                   ) + 1
          )
from t
where $period_start <= end_date and
      $period_end >= start_date;

You can then get a flag by comparing to the number of days:

select (case when sum(datediff(least($period_end, end_date),
                               greatest($period_start, start_date)
                              ) + 1
                     ) =
                  datediff($period_end, $period_start) + 1
             then 1 else 0
        end) as IsAvailableForAllDays
from t
where $period_start <= end_date and
      $period_end >= start_date;

Upvotes: 0

Related Questions