SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Tutorial: Dynamic Views for Complex Date Range Selection

    This is a real-world solution I created for a problem I encountered in development of an appointment-booking platform. I had to get a dynamic date-range (let's say "next 30 days" for this example) and pull availability based both on office hours and scheduled appointments. I left office hours out of this example for simplicity.

    The solution I came up with was to develop a dynamic view based on curdate() functions that would pull a dataset of the next 30 days as dates, and join that to my availability and appointments to pull available time slots for a given business.

    Here's a simplified example of how to accomplish this:

    Assume the following table of appointments:

    Code:
    CREATE TABLE appointment (
        id INT NOT NULL AUTO_INCREMENT,
    	start_date DATETIME NOT NULL,
    	end_date DATETIME NOT NULL,
    	created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    	PRIMARY KEY (id),
    	INDEX (start_date)
    );
    Let's populate it with some data:
    Code:
    INSERT INTO appointment (start_date, end_date)
    VALUES
    	(CONCAT(curdate(), ' 09:00:00'), CONCAT(curdate(), ' 10:00:00')),
    	(CONCAT(curdate() + INTERVAL 1 DAY, ' 09:00:00'), CONCAT(curdate() + INTERVAL 1 DAY, ' 10:00:00')),
    	(CONCAT(curdate() + INTERVAL 1 DAY, ' 11:00:00'), CONCAT(curdate() + INTERVAL 1 DAY, ' 12:00:00')),
    	(CONCAT(curdate() + INTERVAL 15 DAY, ' 13:00:00'), CONCAT(curdate() + INTERVAL 15 DAY, ' 14:00:00')),
    	(CONCAT(curdate() + INTERVAL 18 DAY, ' 19:00:00'), CONCAT(curdate() + INTERVAL 18 DAY, ' 20:00:00'));
    Let's say we want to get a view of the next 30 days' appointments grouped by date, and without missing dates for days with no appointments scheduled.

    First, we can create a dynamic view of the next 30 days, which will auto-update its contents every day:

    Code:
    CREATE OR REPLACE VIEW `next30days` AS 
    SELECT curdate() AS `day`
    UNION ALL SELECT (curdate() + INTERVAL 1 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 2 DAY)
    UNION ALL SELECT (curdate() + INTERVAL 3 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 4 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 5 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 6 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 7 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 8 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 9 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 10 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 11 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 12 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 13 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 14 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 15 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 16 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 17 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 18 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 19 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 20 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 21 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 22 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 23 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 24 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 25 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 26 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 27 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 28 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 29 DAY) 
    UNION ALL SELECT (curdate() + INTERVAL 30 DAY);

    Then, we can left-join our view on the appointments, grouping by day and concatenating the appointments into a simple result.
    Code:
    SELECT n.day, GROUP_CONCAT(CONCAT(TIME(start_date), '-', TIME(end_date))) AS appointments
    FROM next30days n
    LEFT JOIN appointment AS a ON n.day = DATE(a.start_date)
    GROUP BY n.day;
    And our results will look like this:
    Code:
    +------------+-------------------------------------+
    | day        | appointments                        |
    +------------+-------------------------------------+
    | 2011-03-03 | 09:00:00-10:00:00                   |
    | 2011-03-04 | 09:00:00-10:00:00,11:00:00-12:00:00 |
    | 2011-03-05 | NULL                                |
    | 2011-03-06 | NULL                                |
    | 2011-03-07 | NULL                                |
    | 2011-03-08 | NULL                                |
    | 2011-03-09 | NULL                                |
    | 2011-03-10 | NULL                                |
    | 2011-03-11 | NULL                                |
    | 2011-03-12 | NULL                                |
    | 2011-03-13 | NULL                                |
    | 2011-03-14 | NULL                                |
    | 2011-03-15 | NULL                                |
    | 2011-03-16 | NULL                                |
    | 2011-03-17 | NULL                                |
    | 2011-03-18 | 13:00:00-14:00:00                   |
    | 2011-03-19 | NULL                                |
    | 2011-03-20 | NULL                                |
    | 2011-03-21 | 19:00:00-20:00:00                   |
    | 2011-03-22 | NULL                                |
    | 2011-03-23 | NULL                                |
    | 2011-03-24 | NULL                                |
    | 2011-03-25 | NULL                                |
    | 2011-03-26 | NULL                                |
    | 2011-03-27 | NULL                                |
    | 2011-03-28 | NULL                                |
    | 2011-03-29 | NULL                                |
    | 2011-03-30 | NULL                                |
    | 2011-03-31 | NULL                                |
    | 2011-04-01 | NULL                                |
    | 2011-04-02 | NULL                                |
    +------------+-------------------------------------+

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Confused why my only thread in this forum is the only thread with 0 replies.

    Did no one find this useful... or even worthy of commenting on?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •