How to make a SUM with conditions?

I need to create an SQL query for the calculation of hours of employees who work for a specific customer (n°108538). We differentiate two types of counting: those who worked the whole week (37h50) and those who worked on Saturday and Sunday only (22h50).

In order to don't penalize them, it was decided to offer them a 15 hours bonus.

To know who worked weekends only, we use a specific category in their contract, the n°206. Other employees have other categories (001, 250, 604...) so I can't make a simple GROUP BY on that category.

The purpose of the request is to calculate the number of hours worked by all the employee from January 2008 to March 2011, taking into account the hours "offered" to those who work on weekends only.

I thought using EXISTS to know when to add these bonus hours, but I don't get the desired result.

This is the query I wrote:

SELECT   employee.name, employee.surname, SUM(timesheet.hours_par_day + 
            (CASE
                WHEN EXISTS (
                      SELECT *
                        FROM (SELECT contract.contrat_id
                                FROM contract, contract_categories
                               WHERE contract.customer_id = '108538'
                                 AND contract.begin_date BETWEEN '01/01/2008' AND '01/03/2011'
                                 AND contract_categories.contract_id = contract_categories.id_avenant
                                 AND contract_categories.id_category = '206') ctrsd
                       WHERE ctrsd.contrat_id = contract.contrat_id)
                   THEN 15
                ELSE 0
            END
            )
        ) AS hours_worked
FROM contract JOIN employee ON contract.employee_id = employee.employee_id
     JOIN timesheet ON contract.contrat_id = timesheet.contrat_id
WHERE contract.customer_id = '108538'
 AND contract.begin_date BETWEEN '01/01/2008' AND '01/03/2011'
 AND employee .employee_id IN (
                   SELECT employee_id
                     FROM contract
                    WHERE contract.client_id = '108538' AND contract.end_date >= '01/01/2011')

It calculates correctly ... but does 开发者_如何学编程not include the additional 15 hours. I guess I misuse EXISTS, but I don't know what else I can use... does anyone have any idea?

I think in this case you don't need the subquery in the CASE, you can outer join instead (although, I'm not sure where "contract_financial" comes from - maybe a table is missing?):

SELECT   employee.name, employee.surname, SUM(timesheet.hours_par_day + 
            (CASE
                WHEN contract.id_category = '206'
                THEN 15
                ELSE 0
            END
            )
        ) AS hours_worked
FROM contract JOIN employee ON contract.employee_id = employee.employee_id
     JOIN timesheet ON contract.contrat_id = timesheet.contrat_id
     LEFT JOIN contract_categories
     ON contract.contract_id = contract_categories.contract_id
WHERE contract.customer_id = '108538'
 AND contract.begin_date BETWEEN '01/01/2008' AND '01/03/2011'
 AND employee .employee_id IN (
            SELECT employee_id
            FROM contract
            WHERE contract.client_id = '108538'
            AND contract.end_date >= '01/01/2011')