Group a column together where meets several other column conditions

Question

I need to be able to sum a column (hours), where it matches several conditions in other columns. This what the table may look like.

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

The query is shown below:

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

The Week1 and Week2 column would represent hours worked for a 2 week period, and in this case it's set from 2014-05-19 and 2014-06-01. So, the Week1 column would group hours where the agentid, ptid, serviceid match, and the dateincurred would range from 2014-05-19 to 2014-05-25. The same would go for Week2 with dateincurred being between 2014-05-26 to 2014-06-01.

The problem with my query is that I get duplicates, like the last two results shown in the query result below. Week1 should have the 9.00 and Week2 needs to be null; likewise on the row below Week1 should be null and Week2 to be 4.00. The same can be seen on row# 5 & 6. On row 5 Week1 should just be 7.00, 4.00, and 1.00 and Week 2 should be null. Row 6 shouldn't exist.

I know that duplicates exists because the dateincurred field overlap from the query, but I cannot figure any other around it.

Answer 1

If you have the table as shown in the first picture. Then you can easily achieve what you want by this query :

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;