Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT s.`session_id` id,
- DATE_FORMAT(s.`created`,'%m/%d/%Y') date,
- u.`name`,
- COUNT(DISTINCT c.id) calls,
- COUNT(DISTINCT h.id) emails,
- SEC_TO_TIME(MAX(DISTINCT c.duration)) duration,
- (COUNT(DISTINCT em.email_id) + COUNT(DISTINCT pho.phone_id) > 0) status
- FROM `fa_sessions` s
- LEFT JOIN `fa_users` u ON s.`user_id`=u.`user_id`
- LEFT JOIN `fa_email_aliases` a ON a.session_id = s.session_id
- LEFT JOIN `fa_email_headers` h ON h.email_id = a.email_id
- LEFT JOIN `fa_phones` ph ON ph.session_id = s.session_id
- LEFT JOIN `fa_email_aliases` em ON em.session_id = s.session_id AND em.status = 1
- LEFT JOIN `fa_phones` pho ON pho.session_id = s.session_id AND pho.status = 1
- LEFT JOIN `cdr` c ON c.userfield = ph.phone_id
- WHERE s.`partner_id`=1
- GROUP BY s.`session_id`
- SELECT s.`session_id` id,
- DATE_FORMAT(s.`created`,'%m/%d/%Y') date,
- u.`name`,
- COUNT(DISTINCT c.id) calls,
- COUNT(DISTINCT h.id) emails,
- SEC_TO_TIME(MAX(DISTINCT c.duration)) duration,
- (COUNT(DISTINCT em.email_id) + COUNT(DISTINCT pho.phone_id) > 0) status
- FROM `fa_sessions` s
- LEFT JOIN `fa_users` u ON s.`user_id`=u.`user_id`
- LEFT JOIN `fa_email_aliases` a ON a.session_id = s.session_id
- LEFT JOIN `fa_email_headers` h ON h.email_id = a.email_id
- LEFT JOIN `fa_phones` ph ON ph.session_id = s.session_id
- LEFT JOIN `fa_email_aliases` em ON em.session_id = s.session_id AND em.status = 1
- LEFT JOIN `fa_phones` pho ON pho.session_id = s.session_id AND pho.status = 1
- LEFT JOIN `cdr` c ON c.userfield = ph.phone_id
- WHERE s.`partner_id`=1
- GROUP BY s.`session_id`
- fa_sessions ( partner_id, session_id )
- fa_users ( user_id )
- fa_email_aliases ( session_id, email_id )
- fa_email_headers ( email_id )
- fa_email_aliases ( session_id, status )
- fa_phones ( session_id, status, phone_id )
- cdr ( userfield )
Add Comment
Please, Sign In to add comment