Группируйте столбец вместе, где встречается несколько других условий столбца

Question

Мне нужно иметь возможность суммировать столбец (часы), где он соответствует нескольким условиям в других столбцах. Вот как может выглядеть стол.

payrollid, agentid, ptid, serviceid, dateincurred ,hours
209      , 284    , 580 , 1320     , '2014-05-19' ,4
209      , 284    , 569 , 1433     , '2014-45-19' ,2
209      , 284    , 580 , 1433     , '2014-05-19' ,2
209      , 284    , 569 , 1433     , '2014-05-19' ,2
209      , 284    , 580 , 1320     , '2014-05-19' ,7
209      , 284    , 580 ,   19     , '2014-05-20' ,1
209      , 284    , 569 , 1318     , '2014-45-21' ,2
209      , 284    , 580 , 1320     , '2014-45-22' ,1
209      , 284    , 580 ,   16     , '2014-05-23' ,1
209      , 284    , 569 , 1445     , '2014-45-24' ,9
209      , 284    , 580 , 1427     , '2014-05-25' ,1
209      , 284    , 569 , 1326     , '2014-05-28' ,1
209      , 284    , 569 , 1445     , '2014-45-29' ,4
209      , 284    , 569 , 1320     , '2014-05-29' ,1
209      , 284    , 569 , 1347     , '2014-45-29' ,5
209      , 284    , 580 , 1320     , '2014-05-30' ,1
209      , 284    , 569 , 1347     , '2014-05-30' ,5
209      , 284    , 580 , 1326     , '2014-05-31' ,1
209      , 284    , 580 , 1348     , '2014-06-01' ,1
209      , 284    , 580 ,   24     , '2014-06-01' ,1

Запрос показан ниже:

SELECT
    p.agentid,
    p.ptid,
    p.dateincurred,
    p.serviceid,

    (
    SELECT 
        GROUP_CONCAT(p.hours) 
    FROM payroll_detail_temp p3
    WHERE p3.payrollid = '209'
    AND (
            p3.dateincurred BETWEEN '2014-05-19'
            AND DATE_ADD('2014-05-19', INTERVAL 6 DAY))
            AND p3.serviceid = p.serviceid
            GROUP BY
                p3.serviceid
            LIMIT 1
        ) AS week1,

        (
        SELECT
            GROUP_CONCAT(p2.hours)
        FROM
            payroll_detail_temp p2
        WHERE
            p2.payrollid = '209'
        AND (
            p2.dateincurred BETWEEN '2014-05-25'
            AND DATE_ADD('2014-05-25', INTERVAL 6 DAY))
            AND p2.serviceid = p.serviceid
        GROUP BY
            p2.serviceid,
            p2.dateincurred
        LIMIT 1
    ) AS week2,
    GROUP_CONCAT(p.serviceid) AS services,
    GROUP_CONCAT(p.dateincurred) AS dates
FROM
    payroll_detail_temp p
    INNER JOIN agent ON (p.agentid = agent.id)
    INNER JOIN service ON (p.serviceid = service.id)
    INNER JOIN payer ON (p.payerid = payer.id)
    INNER JOIN prov ON (p.provid = prov.id)
    INNER JOIN patient ON (p.ptid = patient.id)
WHERE
    p.payrollid = '209'
    AND (
        p.dateincurred BETWEEN '2014-05-19'
        AND DATE_ADD('2014-05-25', INTERVAL 14 DAY)
    )
GROUP BY
    p.serviceid,
    p.dateincurred

Столбцы Week1 и Week2 будут представлять часы, отработанные в течение двухнедельного периода, и в этом случае они установлены в 2014-05-19 и 2014-06-01. Таким образом, в столбце "Неделя1" будут сгруппированы часы, в которых совпадают значения значения agenttid, ptid, serviceid и даты поступления, с 2014-05-19 по 2014-05-25. То же самое относится и к неделе 2 с датой, которая наступает между 2014-05-26 и 2014-06-01.

Проблема с моим запросом состоит в том, что я получаю дубликаты, как и два последних результата, показанных в результате запроса ниже. Неделя 1 должна иметь 9.00, а Неделя2 должна быть нулевой; аналогично в строке ниже Week1 должно быть null, а Week2 должно быть 4.00. То же самое можно увидеть в строках № 5 и 6. В строке 5 неделя 1 должна быть просто 7,00, 4,00 и 1,00, а неделя 2 должна быть нулевой. Строка 6 не должна существовать.

Я знаю, что дубликаты существуют, потому что поле dateincured перекрывается с запросом, но я не могу найти другие вокруг него.

Answer 1

Если у вас есть таблица, как показано на первом рисунке. Тогда вы можете легко достичь желаемого с помощью этого запроса:

SELECT
 PAYROLLID,
 AGENTID,
 PTID,
 SERVICEID,
 DATEINCURED,
 CASE WHEN (DATEINCURRED BETWEEN '2014-05-19' AND '2014-05-25') THEN SUM(HOURS) END WEEK1,
 CASE WHEN (DATEINCURRED BETWEEN '2014-05-26' AND '2014-06-01') THEN SUM(HOURS) END WEEK2
FROM
 YOUR_TABLE_NAME
GROUP BY
PAYROLLID,
AGENTID;