Creating a calendar view

Databases
1

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,395051,395327#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;

2

Anybody?

3

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

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

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.


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

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

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

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

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.