full outer self join with count for both alias mysql

Question

I have a table diagnoses with 3 fields

 id(PK), created_date(DateTime), doctor_id(Foreign Key).
 doctor1 = 4 (doctor_id), 
 and 
 doctor2= 11(doctor_id).

doctor1 arrives in morning and doctor 2 in evening. As we all know when we run Count(*) function the date that comes is the date of first found record. So I want to print something like this

    Date | Am-TimeIn | Doctor1 (Count) | Pm-TimeIn | Doctor2 (Count)

so that if doctor1 was absent and doctor2 was present, row gets printed, similarly for doctor2'S ABSENCY.

It requires full outer self join , group by clause on Date(created_date) so that all the records sum up on daily basis for each doctor. Currently I have this, but its not working

    SELECT Date(CASE WHEN (a.created_date IS NOT NULL) THEN a.created_date ELSE b.created_date END) 'Date', a.`doctor1`, TIME(a.created_date) 'AM-TimeIn', b.`doctor2`,  TIME(b.created_date) 'PM-TimeIn'
    From (select created_date, count(*) doctor1 FROM diagnoses WHERE  doctor_id = 4 GROUP BY DATE(created_date)) a
    full join
    (select created_date, count(*) doctor2 FROM diagnoses WHERE doctor_id = 11 GROUP BY DATE(created_date)) b
    ON DATE(b.created_date) = DATE(a.created_date);

https://drive.google.com/file/d/0B6pa5Jz8TJ3fN3VLTFVNdFJ1b3hvS0lTaFVTbzNCR2lEV19F/view?usp=sharing this is report with sample data and this is desired output Date | Am-TimeIn | Doctor1 (Count) | Pm-TimeIn | Doctor2 (Count) — Fadia Jabeen, Jan 25 '17 at 07:39
The image report doesn't match the desired output format in your question (or your query) - which do you want? — P.Salmon, Jan 25 '17 at 07:46
That was existing report with actual data. This is dummy data which you require https://drive.google.com/file/d/0B6pa5Jz8TJ3fVHNEbW1Ia0Vrb1k/view?usp=sharing — Fadia Jabeen, Jan 25 '17 at 07:55
I want this "Date | Am-TimeIn | Doctor1 (Count) | Pm-TimeIn | Doctor2 (Count)". Whereas Am-TimeIn = time extracted from created_date, of the first patient checked by doctor1 in the morning. Doctor1 (Count) = count of all patients checked by docotr1 per day. Pm-TimeIn = time extracted from created_date, of the first patient diagnosed by doctor2 in afternoon.and vice versa. — Fadia Jabeen, Jan 25 '17 at 07:58

score 1 · Accepted Answer · answered Jan 25 '17 at 08:05

MariaDB [sandbox]> drop table if exists diagnosis;
Query OK, 0 rows affected (0.09 sec)

MariaDB [sandbox]> create table diagnosis(id int, dt datetime,doctor_id int);
Query OK, 0 rows affected (0.22 sec)

MariaDB [sandbox]> insert into diagnosis values
    -> (1,'2017-01-01 07:00:00',1),(1,'2017-01-01 08:00:00',1),(1,'2017-01-01 13:00:00',2),
    -> (1,'2017-01-02 07:00:00',1),(1,'2017-01-02 08:00:00',1),
    -> (1,'2017-01-03 13:00:00',2);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select   min(case when doctor_id = 1 then doctor_id end) doc1,
    -> min(case when doctor_id = 1 then dt end) am_timein,
    -> sum(case when doctor_id = 1 then 1 else 0 end)  doc1cases,
    -> min(case when doctor_id = 2 then doctor_id end) doc2,
    -> min(case when doctor_id = 2 then dt end) pm_timein,
    -> sum(case when doctor_id = 2 then 1 else 0 end)  doc2cases,
    -> count(*) as Total
    -> from diagnosis
    -> group by date(dt);
+------+---------------------+-----------+------+---------------------+-----------+-------+
| doc1 | am_timein           | doc1cases | doc2 | pm_timein           | doc2cases | Total |
+------+---------------------+-----------+------+---------------------+-----------+-------+
|    1 | 2017-01-01 07:00:00 |         2 |    2 | 2017-01-01 13:00:00 |         1 |     3 |
|    1 | 2017-01-02 07:00:00 |         2 | NULL | NULL                |         0 |     2 |
| NULL | NULL                |         0 |    2 | 2017-01-03 13:00:00 |         1 |     1 |
+------+---------------------+-----------+------+---------------------+-----------+-------+
3 rows in set (0.00 sec)

It works. Lots of Thanks. So happy :) Thanks again. select date(created_date) `Date`, min(case when doctor_id = 4 then Time(created_date) end) `Am-TimeIn`, sum(case when doctor_id = 4 then 1 else 0 end) `Doc1 Cases`, min(case when doctor_id = 11 then Time(created_date) end) `Pm-TimeIn`, sum(case when doctor_id = 11 then 1 else 0 end) `Doc2 Cases`, count(*) as Total from diagnoses where doctor_id in (4,11) group by date(created_date); — Fadia Jabeen, Jan 25 '17 at 08:38

full outer self join with count for both alias mysql

1 Answers1