SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating a calendar view

    I've got a task at work to create a calendar view in mysql. The frontend used is filemaker which doesn't support query results. The only workround is to create a view and use as table.

    What I need is a full calendar view for each date found in appt table for each clinic. Meaning if there is an appoinment in appt table for a given date, I need to produce a full calendar view with all timeslots from timeslots table and then selecting appt rows as columns corresponding each timeslot or null. I'm attached pdf of end result. Don't know where to start.

    I did some googling and found this thread which is similar to what I need but its for only date specified in the where clause. There are preety good senior members on this site. Hoping someone will be able to help me.

    Thank you all,

    http://forums.mysql.com/read.php?108...327#msg-395327

    Select ap.id, ts.time_slot, ap.clinic, ap.appt_date,
    If(ap.doctor='SX', ap.client, null) as Doc_1,
    If(ap.doctor='AOH', ap.client, null) as Doc_2,
    If(ap.doctor='MAN', ap.client, null) as Doc_3,
    If(ap.doctor='DBC', ap.client, null) as Doc_4
    from timeslots ts left outer join appt ap on ts.time_slot = ap.time_slot
    and ts.clinic = ap.clinic
    Group By ts.time_slot, ts.clinic
    Order By ts.id



    CREATE DATABASE `temp`
    CHARACTER SET 'latin1'
    COLLATE 'latin1_swedish_ci';

    USE `temp`;

    DROP TABLE IF EXISTS `appt`;

    CREATE TABLE `appt` (
    `id` int(20) NOT NULL AUTO_INCREMENT,
    `clinic` char(1) CHARACTER SET utf8 DEFAULT NULL,
    `doctor` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
    `appt_date` date DEFAULT NULL,
    `time_slot` varchar(20) DEFAULT NULL,
    `client` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1;

    DROP TABLE IF EXISTS `timeslots`;

    CREATE TABLE `timeslots` (
    `id` int(20) NOT NULL AUTO_INCREMENT,
    `clinic` char(1) DEFAULT NULL,
    `time_slot` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

    INSERT INTO `appt` (`id`, `clinic`, `doctor`, `appt_date`, `time_slot`, `client`) VALUES
    (1,'a','SX','2010-11-24','8:00 am','Bobbie Smith\rTest'),
    (2,'a','AOH','2010-11-24','8:30 am','Molly Matthews\rGot gammy leg'),
    (3,'a','MAN','2010-11-24','9:00 am','client 1'),
    (4,'a','MAN','2010-11-24','9:30 am','client 2'),
    (5,'a','MAN','2010-11-24','10:00 am','client 3'),
    (6,'a','MAN','2010-11-24','11:00 am','client 4'),
    (7,'a','MAN','2010-11-24','11:30 am','client 5'),
    (8,'a','MAN','2010-11-24','12:00 pm','client 6'),
    (9,'a','MAN','2010-11-24','12:30 pm','client 7'),
    (10,'a','MAN','2010-11-24','1:00 pm','client 8'),
    (11,'a','MAN','2010-11-24','1:30 pm','client 9'),
    (12,'a','MAN','2010-11-24','02:00 pm','client 10'),
    (13,'a','MAN','2010-11-24','02:30 pm','client 11'),
    (14,'a','DBC','2010-11-24','8:30 am','Digby Smith\rjhgthjju'),
    (15,'a','MAN','2010-11-24','10:30 am','Jake Smith\rStandard Consultation'),
    (16,'b','SX','2010-11-24','8:00 am','Bobbie Smith\rTest'),
    (17,'b','AOH','2010-11-24','8:30 am','Molly Matthews\rGot gammy leg'),
    (18,'b','MAN','2010-11-24','9:00 am','client 1'),
    (19,'b','MAN','2010-11-24','9:30 am','client 2'),
    (20,'b','MAN','2010-11-24','10:00 am','client 3'),
    (21,'b','MAN','2010-11-24','11:00 am','client 4'),
    (22,'b','MAN','2010-11-24','11:30 am','client 5'),
    (23,'b','MAN','2010-11-24','12:00 pm','client 6'),
    (24,'b','MAN','2010-11-24','12:30 pm','client 7'),
    (25,'b','MAN','2010-11-24','1:00 pm','client 8'),
    (26,'b','MAN','2010-11-24','1:30 pm','client 9'),
    (27,'b','MAN','2010-11-24','02:00 pm','client 10'),
    (28,'b','MAN','2010-11-24','02:30 pm','client 11'),
    (29,'b','DBC','2010-11-24','8:30 am','Digby Smith\rjhgthjju'),
    (30,'b','MAN','2010-11-24','10:30 am','Jake Smith\rStandard Consultation'),
    (31,'a','SX','2010-11-25','8:00 am','Bobbie Smith\rTest'),
    (32,'a','AOH','2010-11-25','8:30 am','Molly Matthews\rGot gammy leg'),
    (33,'a','MAN','2010-11-25','9:00 am','client 1'),
    (34,'a','MAN','2010-11-25','9:30 am','client 2'),
    (35,'a','MAN','2010-11-25','10:00 am','client 3'),
    (36,'a','MAN','2010-11-25','11:00 am','client 4'),
    (37,'a','MAN','2010-11-25','11:30 am','client 5'),
    (38,'a','MAN','2010-11-25','12:00 pm','client 6'),
    (39,'a','MAN','2010-11-25','12:30 pm','client 7'),
    (40,'a','MAN','2010-11-25','1:00 pm','client 8'),
    (41,'a','MAN','2010-11-25','1:30 pm','client 9'),
    (42,'a','MAN','2010-11-25','02:00 pm','client 10'),
    (43,'a','MAN','2010-11-25','02:30 pm','client 11'),
    (44,'a','DBC','2010-11-25','8:30 am','Digby Smith\rjhgthjju'),
    (45,'a','MAN','2010-11-25','10:30 am','Jake Smith\rStandard Consultation'),
    (46,'b','SX','2010-11-25','8:00 am','Bobbie Smith\rTest'),
    (47,'b','AOH','2010-11-25','8:30 am','Molly Matthews\rGot gammy leg'),
    (48,'b','MAN','2010-11-25','9:00 am','client 1'),
    (49,'b','MAN','2010-11-25','9:30 am','client 2'),
    (50,'b','MAN','2010-11-25','10:00 am','client 3'),
    (51,'b','MAN','2010-11-25','11:00 am','client 4'),
    (52,'b','MAN','2010-11-25','11:30 am','client 5'),
    (53,'b','MAN','2010-11-25','12:00 pm','client 6'),
    (54,'b','MAN','2010-11-25','12:30 pm','client 7'),
    (55,'b','MAN','2010-11-25','1:00 pm','client 8'),
    (56,'b','MAN','2010-11-25','1:30 pm','client 9'),
    (57,'b','MAN','2010-11-25','02:00 pm','client 10'),
    (58,'b','MAN','2010-11-25','02:30 pm','client 11'),
    (59,'b','DBC','2010-11-25','8:30 am','Digby Smith\rjhgthjju'),
    (60,'b','MAN','2010-11-25','10:30 am','Jake Smith\rStandard Consultation');
    COMMIT;

    INSERT INTO `timeslots` (`id`, `clinic`, `time_slot`) VALUES
    (1,'a','09:00 am'),
    (2,'a','09:30 am'),
    (3,'a','10:00 am'),
    (4,'a','10:30 am'),
    (5,'a','11:00 am'),
    (6,'a','11:30 am'),
    (7,'a','12:00 pm'),
    (8,'a','12:30 pm'),
    (9,'a','01:00 pm'),
    (10,'a','01:30 pm'),
    (11,'a','02:00 pm'),
    (12,'a','02:30 pm'),
    (13,'a','03:00 pm'),
    (14,'a','03:30 pm'),
    (15,'a','04:00 pm'),
    (16,'a','04:30 pm'),
    (17,'a','05:00 pm'),
    (18,'b','09:00 am'),
    (19,'b','09:30 am'),
    (20,'b','10:00 am'),
    (21,'b','10:30 am'),
    (22,'b','11:00 am'),
    (23,'b','11:30 am'),
    (24,'b','12:00 pm'),
    (25,'b','12:30 pm'),
    (26,'b','01:00 pm'),
    (27,'b','01:30 pm'),
    (28,'b','02:00 pm'),
    (29,'b','02:30 pm'),
    (30,'b','03:00 pm'),
    (31,'b','03:30 pm'),
    (32,'b','04:00 pm'),
    (33,'b','04:30 pm'),
    (34,'b','05:00 pm');
    COMMIT;
    Attached Files Attached Files

  2. #2
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anybody?

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    By view do you mean a MySQL view or are you able to use an application language to format the results?

    Also, do you only have to account for the four doctors, or is there a possibility of more? It seems like you number and names of doctors will vary. However, in your query you are referencing the four directly.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    One of issues that I came across is because the data is not consistent ie. 08:30 and 8:30 there is a considerable amount of logic that needs to be added. Is it possible to always make sure to either use 08:30 or 8:30?

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This seems to work, with the inconsistent dates. Your going to want to add some indexes. The only thing it won't do is separate out each doctor into a separate column. Instead the clients and docs are grouped in a 1:1 mapping in order of the doctors name. I also added a replace for the line break, just for testing purposes – to see the results in workbench. Though it can be removed. Might also want a different separator for GROUP_CONCAT() if its possible for people to enter commas, perhaps a pipe or backtick would be a choice less prone to issues.

    Pretty much what it does in a nutshell is first it gets all the days with appointments for each clinic. Than each day is joined with all the available time slots for the clinic. Than for each full time combination every doctor in the clinic is joined. Than using that combination appointments are found that actually exist for every day/time/clinic/doc combo. The sub-queries are mainly used to normalize the bad data that you seem to working with ie. 8:30 and 08:30 and the dates as well. So that a time slot that is 8:30 properly maps to a appointment that is 08:30 and vice-versa.

    Code SQL:
    SELECT
          clinic_days.clinic
         ,TIME_FORMAT(clinic_hours.hour_min,'%h:%i %p') time_slot
         ,DATE_FORMAT(clinic_days.norm_date,'%d/%m/%Y') appt_date
         ,GROUP_CONCAT(clinic_docs.doctor ORDER BY clinic_docs.doctor ASC) docs
         ,GROUP_CONCAT(REPLACE(COALESCE(clinic_docs_appt.`client`,'--'),'\r','')) clients
         ,CONCAT(clinic_days.norm_date,' ',clinic_hours.hour_min) full_time
      FROM
         (SELECT
               clinic
               ,STR_TO_DATE(appt_date,'%Y-%m-%d') AS norm_date
            FROM
               appt
           GROUP
              BY
               clinic
               ,norm_date) clinic_days
    LEFT OUTER
    JOIN
         (SELECT
                clinic
               ,STR_TO_DATE(time_slot,'%h:%i %p') hour_min
            FROM
               timeslots
           GROUP
              BY
                clinic
               ,hour_min) clinic_hours  
       ON
        clinic_days.clinic = clinic_hours.clinic
    LEFT OUTER
    JOIN
       (SELECT
             clinic
             ,doctor
          FROM
             appt
         GROUP
            BY
             clinic
             ,doctor) clinic_docs
       ON
         clinic_days.clinic = clinic_docs.clinic
    LEFT OUTER
    JOIN
       (SELECT
              clinic
              ,doctor
              ,`client`
             ,STR_TO_DATE(appt_date,'%Y-%m-%d') AS norm_date
             ,STR_TO_DATE(time_slot,'%h:%i %p') hour_min
          FROM
             appt) clinic_docs_appt
       ON
        clinic_days.norm_date  = clinic_docs_appt.norm_date
      AND
        clinic_hours.hour_min  = clinic_docs_appt.hour_min
      AND
        clinic_docs.clinic     = clinic_docs_appt.clinic
      AND
        clinic_docs.doctor     = clinic_docs_appt.doctor
    GROUP
       BY
        clinic
        ,full_time
    ORDER
       BY
         clinic ASC
        ,full_time ASC

  6. #6
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    One of issues that I came across is because the data is not consistent ie. 08:30 and 8:30 there is a considerable amount of logic that needs to be added. Is it possible to always make sure to either use 08:30 or 8:30?
    yes its possible to use always 08:30. Pasting another set of data.
    Thanks!

    Truncate appt;
    INSERT INTO `appt` (`id`, `clinic`, `doctor`, `appt_date`, `time_slot`, `client`) VALUES
    (3,'a','MAN','2010-11-24','09:00 am','client 1'),
    (4,'a','MAN','2010-11-24','09:30 am','client 2'),
    (5,'a','MAN','2010-11-24','10:00 am','client 3'),
    (6,'a','MAN','2010-11-24','11:00 am','client 4'),
    (7,'a','MAN','2010-11-24','11:30 am','client 5'),
    (8,'a','MAN','2010-11-24','12:00 pm','client 6'),
    (9,'a','MAN','2010-11-24','12:30 pm','client 7'),
    (10,'a','MAN','2010-11-24','01:00 pm','client 8'),
    (11,'a','MAN','2010-11-24','01:30 pm','client 9'),
    (12,'a','MAN','2010-11-24','02:00 pm','client 10'),
    (13,'a','MAN','2010-11-24','02:30 pm','client 11'),
    (15,'a','MAN','2010-11-24','10:30 am','Jake Smith\rStandard Consultation'),
    (18,'b','MAN','2010-11-24','09:00 am','client 1'),
    (19,'b','MAN','2010-11-24','09:30 am','client 2'),
    (20,'b','MAN','2010-11-24','10:00 am','client 3'),
    (21,'b','MAN','2010-11-24','11:00 am','client 4'),
    (22,'b','MAN','2010-11-24','11:30 am','client 5'),
    (23,'b','MAN','2010-11-24','12:00 pm','client 6'),
    (24,'b','MAN','2010-11-24','12:30 pm','client 7'),
    (25,'b','MAN','2010-11-24','01:00 pm','client 8'),
    (26,'b','MAN','2010-11-24','01:30 pm','client 9'),
    (27,'b','MAN','2010-11-24','02:00 pm','client 10'),
    (28,'b','MAN','2010-11-24','02:30 pm','client 11'),
    (30,'b','MAN','2010-11-24','10:30 am','Jake Smith\rStandard Consultation'),
    (33,'a','MAN','2010-11-25','09:00 am','client 1'),
    (34,'a','MAN','2010-11-25','09:30 am','client 2'),
    (35,'a','MAN','2010-11-25','10:00 am','client 3'),
    (36,'a','MAN','2010-11-25','11:00 am','client 4'),
    (37,'a','MAN','2010-11-25','11:30 am','client 5'),
    (38,'a','MAN','2010-11-25','12:00 pm','client 6'),
    (39,'a','MAN','2010-11-25','12:30 pm','client 7'),
    (40,'a','MAN','2010-11-25','01:00 pm','client 8'),
    (41,'a','MAN','2010-11-25','01:30 pm','client 9'),
    (42,'a','MAN','2010-11-25','02:00 pm','client 10'),
    (43,'a','MAN','2010-11-25','02:30 pm','client 11'),
    (45,'a','MAN','2010-11-25','10:30 am','Jake Smith\rStandard Consultation'),
    (48,'b','MAN','2010-11-25','09:00 am','client 1'),
    (49,'b','MAN','2010-11-25','09:30 am','client 2'),
    (50,'b','MAN','2010-11-25','10:00 am','client 3'),
    (51,'b','MAN','2010-11-25','11:00 am','client 4'),
    (52,'b','MAN','2010-11-25','11:30 am','client 5'),
    (53,'b','MAN','2010-11-25','12:00 pm','client 6'),
    (54,'b','MAN','2010-11-25','12:30 pm','client 7'),
    (55,'b','MAN','2010-11-25','01:00 pm','client 8'),
    (56,'b','MAN','2010-11-25','01:30 pm','client 9'),
    (57,'b','MAN','2010-11-25','02:00 pm','client 10'),
    (58,'b','MAN','2010-11-25','02:30 pm','client 11'),
    (60,'b','MAN','2010-11-25','10:30 am','Jake Smith\rStandard Consultation');
    COMMIT;

  7. #7
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    This seems to work, with the inconsistent dates. Your going to want to add some indexes. The only thing it won't do is separate out each doctor into a separate column. Instead the clients and docs are grouped in a 1:1 mapping in order of the doctors name. I also added a replace for the line break, just for testing purposes – to see the results in workbench. Though it can be removed. Might also want a different separator for GROUP_CONCAT() if its possible for people to enter commas, perhaps a pipe or backtick would be a choice less prone to issues.

    Pretty much what it does in a nutshell is first it gets all the days with appointments for each clinic. Than each day is joined with all the available time slots for the clinic. Than for each full time combination every doctor in the clinic is joined. Than using that combination appointments are found that actually exist for every day/time/clinic/doc combo. The sub-queries are mainly used to normalize the bad data that you seem to working with ie. 8:30 and 08:30 and the dates as well. So that a time slot that is 8:30 properly maps to a appointment that is 08:30 and vice-versa.

    Code SQL:
    SELECT
          clinic_days.clinic
         ,TIME_FORMAT(clinic_hours.hour_min,'%h:%i %p') time_slot
         ,DATE_FORMAT(clinic_days.norm_date,'%d/%m/%Y') appt_date
         ,GROUP_CONCAT(clinic_docs.doctor ORDER BY clinic_docs.doctor ASC) docs
         ,GROUP_CONCAT(REPLACE(COALESCE(clinic_docs_appt.`client`,'--'),'\r','')) clients
         ,CONCAT(clinic_days.norm_date,' ',clinic_hours.hour_min) full_time
      FROM
         (SELECT
               clinic
               ,STR_TO_DATE(appt_date,'%Y-%m-%d') AS norm_date
            FROM
               appt
           GROUP
              BY
               clinic
               ,norm_date) clinic_days
    LEFT OUTER
    JOIN
         (SELECT
                clinic
               ,STR_TO_DATE(time_slot,'%h:%i %p') hour_min
            FROM
               timeslots
           GROUP
              BY
                clinic
               ,hour_min) clinic_hours  
       ON
        clinic_days.clinic = clinic_hours.clinic
    LEFT OUTER
    JOIN
       (SELECT
             clinic
             ,doctor
          FROM
             appt
         GROUP
            BY
             clinic
             ,doctor) clinic_docs
       ON
         clinic_days.clinic = clinic_docs.clinic
    LEFT OUTER
    JOIN
       (SELECT
              clinic
              ,doctor
              ,`client`
             ,STR_TO_DATE(appt_date,'%Y-%m-%d') AS norm_date
             ,STR_TO_DATE(time_slot,'%h:%i %p') hour_min
          FROM
             appt) clinic_docs_appt
       ON
        clinic_days.norm_date  = clinic_docs_appt.norm_date
      AND
        clinic_hours.hour_min  = clinic_docs_appt.hour_min
      AND
        clinic_docs.clinic     = clinic_docs_appt.clinic
      AND
        clinic_docs.doctor     = clinic_docs_appt.doctor
    GROUP
       BY
        clinic
        ,full_time
    ORDER
       BY
         clinic ASC
        ,full_time ASC
    Hey buddy,

    separating out each doctor into a separate column is a must. Perhaps you can come up with some solution. Thanks for trying.

  8. #8
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    By view do you mean a MySQL view or are you able to use an application language to format the results?

    Also, do you only have to account for the four doctors, or is there a possibility of more? It seems like you number and names of doctors will vary. However, in your query you are referencing the four directly.
    I mean Mysql view. Fronend app doesn't support executing query and fetching result (Filemaker). The only workaround is to create view in mysql and use it as a table.

    There will be 8 doctors (columns) max. You can pre assign 1 doctor (doc_1, doc_2 to doc_8) for each column in the query. I'll change the doctor name as reqd.

    Thanks!

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ch5055
    separating out each doctor into a separate column is a must. Perhaps you can come up with some solution. Thanks for trying.
    That is going to require dynamic SQL. Not entirely sure how you would go about that to be honest. I would normally just handle it at the application level, building a query dynamically based on the number of doctors.


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
  •