CODEWITHSUNDEEP
mysqlsqldatabase
brux
brux

Reputation: 3230

Using DateTime functions to check for subscriptions that fall on booking date

I have the following tables

Subscription

id  startdate   spaceid vehicleid buyerid
3   2014-04-23     1       1       4    
4   2014-05-20     1       2       5 


SubscribedDays

id  mon     tue     wed     thur    fri     sat     sun     subscriptionid
1   1        0       0       0       1       0       0       3
2   0        0       1       0       0       0       0       4

The first table defines when a subscription starts and which space the subscription belongs to.

The second table defines the days that are part of a subscription.

There is also a Booking table that hold individual bookings but its not important here.

When placing a booking I want to first check to see if there are any subscriptions that would clash with the booking, so I need to query on the above 2 tables, providing the startdate that the booking is meant for and a spaceid, and check to see if any subscriptions exist that would prevent the booking from taking place, and return true or false accordingly. Can i do this with the current table definitions?

Upvotes: 0

Views: 49

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

This will show any/all conflicts given a date and particular date and space id:

SELECT
    s.startdate,
    d.*
FROM
    subscription s
JOIN subscribeddays d ON s.id = d.subscriptionid
RIGHT JOIN (
    SELECT
        '2014-04-28' AS c_dt,
        dayofweek('2014-04-28') AS c_dt_wk,
        1 AS c_space
    FROM
        DUAL
) c ON c.c_space = s.spaceid
AND s.startdate <= c.c_dt
AND (
    (c.c_dt_wk = 1 AND d.sun = 1)
    OR (c.c_dt_wk = 2 AND d.mon = 1)
    OR (c.c_dt_wk = 3 AND d.tue = 1)
    OR (c.c_dt_wk = 4 AND d.wed = 1)
    OR (c.c_dt_wk = 5 AND d.thur = 1)
    OR (c.c_dt_wk = 6 AND d.fri = 1)
    OR (c.c_dt_wk = 7 AND d.sat = 1)
)

I used: '2014-04-28' for the date, 1 for the space id (change these to variables in sub c)

It will show any subscriptions at that space that have already started by the prompted date and which have a 1 value for the day of week of the prompted date.

There is one problem, you do not have an end date recorded. So subscriptions that may have ended will still show up as conflicts even though they don't actually present conflicts. I would add an end date to your subscriptions table and then include that one extra line of criteria to the above query to avoid subscriptions that have ended from appearing as conflicts.

SQL fiddle test is here: http://sqlfiddle.com/#!2/62fefd/9/0

Note that April 28th, 2014 is a Monday. And I used spaceid 1 (see sub c). That conflicts w/ subscription #3 because it is also at spaceid 1 and it has a value of 1 in the MON column (the 28th is a Monday).

Try changing the values in sub c to test it out.

(note that if there are no conflicts, you'll just get 1 row of nulls back)

Upvotes: 1

Related Questions