gistfile1.sql

SELECT `planDays`.`projectId` AS `projectId`,
       `planDays`.`allocationId` AS `allocationId`,
       ROUND(SUM(`planDays`.`hours` *
                   (SELECT `rates`.`cost`
                    FROM `rates`
                    INNER JOIN `teamMemberGrades` ON `rates`.`id` = `teamMemberGrades`.`rateId`
                    AND `teamMemberGrades`.`teamMemberId` = `planDays`.`teamMemberId`
                    WHERE `teamMemberGrades`.`startDate` IS NULL
                      OR `planDays`.`day` >= `teamMemberGrades`.`startDate`
                    ORDER BY `teamMemberGrades`.`startDate` DESC LIMIT 1)), 2) AS `value`
FROM `planDays`
INNER JOIN `allocations` ON `planDays`.`allocationId` = `allocations`.`id`
INNER JOIN `projects` ON `allocations`.`projectId` = `projects`.`id`
INNER JOIN `teamMembers` ON `planDays`.`teamMemberId` = `teamMembers`.`id`
WHERE `projects`.`portfolioId` = 1
  AND `teamMembers`.`thirdParty` = 1
  AND `planDays`.`day` <= '2013-01-01'
GROUP BY `planDays`.`projectId`,
         `planDays`.`allocationId`