Data Customer Date Clinic Name AppointID Scenario: We send out appointment reminder calls to our customers for their upcoming appointments with our cliniics. Since patients can have multiple...

Data
    Customer    Date    Clinic Name    AppointID        Scenario: We send out appointment reminder calls to our customers for their upcoming appointments with our cliniics. Since patients can have multiple appointments with the same or different clinics, we want to send only one reminder call for all appointments occuring on the same day.
    A
y    2/1/15    A    1001
    A
y    2/1/15    A    1002        Question from Manager: S/He wants to know the percentage of reminder calls that are for multiple appointments.
    A
y    2/1/15    B    1003
    Brian    2/1/15    B    1004
    Brian    2/1/15    C    1005
    Brian    2/1/15    B    1014
    Cathy    2/1/15    C    1006
    A
y    2/2/15    D    1007
    Brian    2/2/15    D    1008
    Brian    2/2/15    D    1009
    Cathy    2/2/15    A    1010
    David    2/3/15    A    1015
    Brian    2/4/15    B    1013
    David    2/4/15    B    1011
    David    2/4/15    C    1012
    David    2/4/15    B    1016
Analysis
    Row Labels    Count of AppointID                Customer    Date    Count of Unique Appointments
    A
y    4                                No. of multiple appointments    11
    2/1/15    3                A
y    2/1/15    3
    2/2/15    1                    2/2/15    1        No. of single appointments    5
    Brian    6
    2/1/15    3                Brian    2/1/15    3
    2/2/15    2                    2/2/15    2        Probability of multiple appointments    0.6875    68.75%
    2/4/15    1                    2/4/15    1        Probability of single appointments    0.3125    31.25%
    Cathy    2
    2/1/15    1                Cathy    2/1/15    1
    2/2/15    1                    2/2/15    1
    David    4
    2/3/15    1                David    2/3/15    1
    2/4/15    3                    2/4/15    3
    Grand Total    16                    Total    16
APPOINTMENTS
2/1/15    2/2/15    2/1/15    2/2/15    2/4/15    2/1/15    2/2/15    2/3/15    2/4/15    A
y    Brian    Cathy    David    3    1    3    2    1    1    1    1    3    

ANALYSIS
Scenario: We send out appointment reminder calls to our customers for their upcoming appointments with our clinics. Since patients can have multiple appointments with the same or different clinics, we want to send only one reminder call for all appointments occu
ing on the same day.                 
    Custome
    Date
    Clinic Name
    Appointment ID
    A
y
    2/1/2015
    A
    1001
    A
y
    2/1/2015
    A
    1002
    A
y
    2/1/2015
    B
    1003
    Brian
    2/1/2015
    B
    1004
    Brian
    2/1/2015
    C
    1005
    Brian
    2/1/2015
    B
    1014
    Cathy
    2/1/2015
    C
    1006
    A
y
    2/2/2015
    D
    1007
    Brian
    2/2/2015
    D
    1008
    Brian
    2/2/2015
    D
    1009
    Cathy
    2/2/2015
    A
    1010
    David
    2/3/2015
    A
    1015
    Brian
    2/4/2015
    B
    1013
    David
    2/4/2015
    B
    1011
    David
    2/4/2015
    C
    1012
    David
    2/4/2015
    B
    1016
        
We can see from the given table that from Fe
uary 1st to 4th appointment details given for customer in same or different clinic.
It has been decided that since patients can have multiple appointments with the same or different clinics, we want to send only one reminder call for all appointments occu
ing on the same day.
So, we need to find how many multiple appointments & single appointments.
Using pivot table, we can organize the table to get better visualization of customer with appointments in same or different clinics on same date.
    Row Labels
    Count of Appointments
    A
y
    4
    2/1/2015
    3
    2/2/2015
    1
    Brian
    6
    2/1/2015
    3
    2/2/2015
    2
    2/4/2015
    1
    Cathy
    2
    2/1/2015
    1
    2/2/2015
    1
    David
    4
    2/3/2015
    1
    2/4/2015
    3
    Grand Total
    16
    Custome
    Date
    Count of Unique Appointments
     
    A
y
    2/1/2015
    3
    
    2/2/2015
    1
     
    
     
    Brian
    2/1/2015
    3
    
    2/2/2015
    2
    
    2/4/2015
    1
     
    
     
    Cathy
    2/1/2015
    1
    
    2/2/2015
    1
     
    
     
    David
    2/3/2015
    1
    
    2/4/2015
    3
     
    Total
    16
No. of multiple appointments: 11    
No. of single appointments: 5    
        
Total number of appointments = 16        
Probability of multiple appointments = 11/16 = 0.6875 = 68.75%
Probability of single appointments = 5/16 = 0.3125 = 31.25%         
    
GRAPH                    
    
                                                    
APPOINTMENTS
2/1/2015    2/2/2015    2/1/2015    2/2/2015    2/4/2015    2/1/2015    2/2/2015    2/3/2015    2/4/2015    A
y    Brian    Cathy    David    3    1    3    2    1    1    1    1    3    
APPOINTMENTS
Probability of multiple appointments    Probability of single appointments    0.6875    0.3125