gistfile1.sql

SELECT `Salesperson`.`id`, 
       `Salesperson`.`company_id`, 
       (SELECT Sum(project_item_allocations.amount / 
                   project_items.salesperson_count) 
        FROM   project_item_allocations 
               JOIN project_item_sellers 
                 ON ( project_item_allocations.project_item_id = 
                      project_item_sellers.project_item_id ) 
               JOIN project_items 
                 ON ( project_items.id = project_item_sellers.project_item_id ) 
        WHERE  project_item_sellers.salesperson_id = salesperson.id 
               AND Year(project_item_allocations.created) = 2014 
               AND Month(project_item_allocations.created) = 03 
               AND project_item_allocations.company_id = salesperson.company_id) 
       AS 
       thismonth, 
       (SELECT Sum(project_item_allocations.amount / 
                   project_items.salesperson_count) 
        FROM   project_item_allocations 
               JOIN project_item_sellers 
                 ON ( project_item_allocations.project_item_id = 
                      project_item_sellers.project_item_id ) 
               JOIN project_items 
                 ON ( project_items.id = project_item_sellers.project_item_id ) 
        WHERE  project_item_sellers.salesperson_id = salesperson.id 
               AND Year(project_item_allocations.created) = 2014 
               AND Month(project_item_allocations.created) = 02 
               AND project_item_allocations.company_id = salesperson.company_id) 
       AS 
       lastmonth, 
       (SELECT Sum(project_item_allocations.amount / 
                   project_items.salesperson_count) 
        FROM   project_item_allocations 
               JOIN project_item_sellers 
                 ON ( project_item_allocations.project_item_id = 
                      project_item_sellers.project_item_id ) 
               JOIN project_items 
                 ON ( project_items.id = project_item_sellers.project_item_id ) 
        WHERE  project_item_sellers.salesperson_id = salesperson.id 
               AND project_items.created >= '2014-02-14' 
               AND project_item_allocations.company_id = salesperson.company_id) 
       AS 
       last30days 
FROM   `budget`.`users` AS `Salesperson` 
WHERE  `Salesperson`.`company_id` = 'nodes-dk' 
ORDER  BY `Salesperson`.`name` ASC