gistfile1.txt

select count(DISTINCT(appointments.id)) from appointments LEFT JOIN pictures ON pictures.appointment_id = appointments.id LEFT JOIN routes ON appointments.route_id = routes.id WHERE pictures.created_at IS NOT NULL AND DATE(CONVERT_TZ(MIN(pictures.created_at), 'UTC', 'US/Eastern')) != routes.date GROUP BY appointments.id;