gistfile1.sql

SELECT   booking_campaign.cv_id,
         booking_campaign.psychometric_id,
         booking_campaign.is_completed,
         booking_campaign.fullname,
         booking_campaign.candidate_status,
         question_point.score_percen,
         candidate_extra_service.iq_tests,
         candidate_extra_service.iq_tests_service_cost,
         candidate_extra_service.reference_checks,
         candidate_extra_service.reference_checks_service_cost,
         candidate_extra_service.police_checks,
         candidate_extra_service.police_checks_service_cost,
         candidate_extra_service.physcometric_tests,
         candidate_extra_service.physcometric_tests_service_cost,
         candidate_extra_service.phone_interviews,
         candidate_extra_service.phone_interviews_service_cost,
         candidate_extra_service.advanced_skills_tests,
         candidate_extra_service.advanced_skills_tests_service_cost,
         website_psychometric_counted.psychometric_id_counted
FROM     (SELECT DISTINCT bk.booking_id,
                          wcv.cv_id,
                          wcv.is_completed,
                          wcv.status    AS candidate_status,
                          wr.firstname,
                          wr.surname,
                          wr.display_as AS fullname,
                          bwcv.psychometric_id
          FROM   booking_website_cv AS bwcv
                 INNER JOIN booking AS bk
                   ON bk.booking_id = bwcv.booking_id
                 INNER JOIN website_cv AS wcv
                   ON bwcv.cv_id = wcv.cv_id
                 INNER JOIN website_registers AS wr
                   ON wr.website_register_id = wcv.website_register_id
          WHERE  bk.booking_id = '43681'
                 AND 1 = 1
                 AND (wcv.submitted_date BETWEEN '2007-12-25' AND '2009-12-14')) booking_campaign
         LEFT JOIN (SELECT wcv.cv_id,
                           Coalesce(total_score_qsn,0)                 AS total_score_qsn,
                           Coalesce(total_of_question_point_qsn,0)     AS total_of_question_point_qsn,
                           Coalesce(total_score_service,0)             AS total_score_service,
                           Coalesce(total_of_question_point_service,0) AS total_of_question_point_service,
                           Coalesce(Round(((Coalesce(total_score_qsn,0) + Coalesce(total_score_service,0)) / (Coalesce(total_of_question_point_qsn,0) + Coalesce(total_of_question_point_service,0))) * 100),
                                    0) AS score_percen
                    FROM   website_cv wcv
                           LEFT JOIN (SELECT qry_total_score_qsn.cv_id,
                                             qry_total_score_qsn.total_score_qsn,
                                             qry_total_of_question_point_qsn.total_of_question_point_qsn
                                      FROM   (SELECT   wcv.cv_id,
                                                       Coalesce(Sum(wcva.total_point),0) AS total_score_qsn,
                                                       booking_questionnaire.q_id
                                              FROM     website_cv AS wcv
                                                       LEFT JOIN website_cv_answer AS wcva
                                                         ON wcv.cv_id = wcva.cv_id
                                                       INNER JOIN booking_question AS bq
                                                         ON bq.question_id = wcva.question_id
                                                       INNER JOIN booking_questionnaire
                                                         ON bq.q_id = booking_questionnaire.q_id
                                              WHERE    bq.question_has_point = 'Y'
                                                       AND booking_questionnaire.service_id IS NULL
                                              GROUP BY wcv.cv_id) qry_total_score_qsn
                                             INNER JOIN (SELECT   bqn.q_id,
                                                                  Coalesce(Sum(bq.question_total_of_point),0) AS total_of_question_point_qsn
                                                         FROM     booking_questionnaire AS bqn
                                                                  INNER JOIN booking_question AS bq
                                                                    ON bqn.q_id = bq.q_id
                                                         WHERE    bq.question_has_point = 'Y'
                                                                  AND bqn.service_id IS NULL
                                                         GROUP BY bqn.q_id) qry_total_of_question_point_qsn
                                               ON qry_total_score_qsn.q_id = qry_total_of_question_point_qsn.q_id) wcv_total_point_qsn
                             ON wcv.cv_id = wcv_total_point_qsn.cv_id
                           LEFT JOIN (SELECT qry_total_score_service.cv_id,
                                             qry_total_score_service.total_score_service,
                                             qry_total_of_question_point_service.total_of_question_point_service
                                      FROM   (SELECT   website_cv.cv_id,
                                                       Coalesce(Sum(website_cv_answer.total_point),0) AS total_score_service
                                              FROM     booking_extra_service
                                                       INNER JOIN booking_questionnaire
                                                         ON booking_extra_service.q_id = booking_questionnaire.q_id
                                                       INNER JOIN booking_question
                                                         ON booking_questionnaire.q_id = booking_question.q_id
                                                       INNER JOIN website_cv
                                                         ON website_cv.cv_id = booking_extra_service.cv_id
                                                       INNER JOIN website_cv_answer
                                                         ON website_cv.cv_id = website_cv_answer.cv_id
                                                            AND website_cv_answer.question_id = booking_question.question_id
                                              WHERE    booking_extra_service.display_on = 'C'
                                                       AND booking_extra_service.status = 'C'
                                                       AND booking_question.question_has_point = 'Y'
                                                       AND booking_question.q_answer_type <> 'dr'
                                              GROUP BY website_cv.cv_id) qry_total_score_service
                                             LEFT JOIN (SELECT   website_cv.cv_id,
                                                                 Coalesce(Sum(booking_question.question_total_of_point),
                                                                          0) AS total_of_question_point_service
                                                        FROM     booking_extra_service
                                                                 INNER JOIN booking_questionnaire
                                                                   ON booking_extra_service.q_id = booking_questionnaire.q_id
                                                                 INNER JOIN booking_question
                                                                   ON booking_questionnaire.q_id = booking_question.q_id
                                                                 INNER JOIN website_cv
                                                                   ON website_cv.cv_id = booking_extra_service.cv_id
                                                        WHERE    booking_extra_service.display_on = 'C'
                                                                 AND booking_extra_service.status = 'C'
                                                                 AND booking_question.question_has_point = 'Y'
                                                                 AND booking_question.q_answer_type <> 'dr'
                                                        GROUP BY website_cv.cv_id) qry_total_of_question_point_service
                                               ON qry_total_score_service.cv_id = qry_total_of_question_point_service.cv_id) wcv_total_point_service
                             ON wcv_total_point_qsn.cv_id = wcv_total_point_service.cv_id) question_point
           ON booking_campaign.cv_id = question_point.cv_id
         LEFT JOIN (SELECT DISTINCT candidate_cv.cv_id,
                                    select_iq_tests.iq_tests,
                                    select_iq_tests.iq_tests_service_cost,
                                    select_reference_checks.reference_checks,
                                    select_reference_checks.reference_checks_service_cost,
                                    select_police_checks.police_checks,
                                    select_police_checks.police_checks_service_cost,
                                    select_physcometric_tests.physcometric_tests,
                                    select_physcometric_tests.physcometric_tests_service_cost,
                                    select_phone_interviews.phone_interviews,
                                    select_phone_interviews.phone_interviews_service_cost,
                                    select_advanced_skills_tests.advanced_skills_tests,
                                    select_advanced_skills_tests.advanced_skills_tests_service_cost
                    FROM   candidate_cv
                           LEFT JOIN select_iq_tests
                             ON candidate_cv.cv_id = select_iq_tests.cv_id
                           LEFT JOIN select_reference_checks
                             ON candidate_cv.cv_id = select_reference_checks.cv_id
                           LEFT JOIN select_police_checks
                             ON candidate_cv.cv_id = select_police_checks.cv_id
                           LEFT JOIN select_physcometric_tests
                             ON candidate_cv.cv_id = select_physcometric_tests.cv_id
                           LEFT JOIN select_phone_interviews
                             ON candidate_cv.cv_id = select_phone_interviews.cv_id
                           LEFT JOIN select_advanced_skills_tests
                             ON candidate_cv.cv_id = select_advanced_skills_tests.cv_id
                    WHERE  candidate_cv.cv_id IS NOT NULL) candidate_extra_service
           ON booking_campaign.cv_id = candidate_extra_service.cv_id
         LEFT JOIN (SELECT   Count(wb_p.psychometric_id) AS psychometric_id_counted,
                             wb_cv.cv_id
                    FROM     website_psychometric wb_p
                             INNER JOIN website_cv wb_cv
                               ON wb_cv.web_id = wb_p.website_id
                    GROUP BY wb_cv.cv_id) website_psychometric_counted
           ON booking_campaign.cv_id = website_psychometric_counted.cv_id
WHERE    1 = 1
ORDER BY question_point.score_percen DESC,
         booking_campaign.fullname,
         booking_campaign.cv_id DESC