Fun with SQL

I recently had to write a webservice that grabs data from our time tracking system (timeforce) and wraps it in an easily consumable manner for importing into Salesforce. The data had to be grouped by department with, and have any department that isn’t represented have all the hours dumped into a catch all category. Each department charges their own rate, so I had to do some math on the fly to find the the totals for each department. Also had to make sure there were no nulls, they had to be replaced with zeros. It involves 2 subqueries (the top selects from a select that selects from another select…). The query below is what I finally ended up with just through trial, error, and reverse engineering the timeforce database. They really aren’t very helpful when it comes to trying to understand their data model so I am pretty proud that I managed to get this deep an understanding of it through just poking around. This is mostly just a reminder post for me, so if in the future I’m like ‘how the hell did I do that?’ I can find it. It might help others who work with timeforce, or just want to gawk at an absurd query. There is a little bit of coldfusion at the the end to apply an optional filter.

SELECT 
    ISNULL(Min(Case when DEPARTMENTNAME = 'Account Managers' then AMOUNT  end), 0.00) as 'Cost_Account_Managers__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Data Managers' then AMOUNT  end), 0.00) as 'Cost_Data_Managers__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Field Managers' then AMOUNT  end), 0.00) as 'Cost_Field_Managers__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Information Technology' then AMOUNT  end), 0.00) as 'Cost_Administration_Information_Techno__c',   
    ISNULL(Min(Case when DEPARTMENTNAME = 'Operations-Office' then AMOUNT  end), 0.00) as 'Cost_Operations_Office__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Project Mgmt' then AMOUNT  end), 0.00) as 'Cost_Operations_Office__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Recruiting-Coordinators' then AMOUNT  end), 0.00) as 'Cost_Recruiting_Coordinators__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Recruiting-Callers' then AMOUNT  end), 0.00) as 'Cost_Recruiting_Callers__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Relationship Manager' then AMOUNT end), 0.00) as 'Cost_Relationship_Manager__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Sales' then AMOUNT  end), 0.00) as 'Cost_Sales__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Finance' then AMOUNT  end), 0.00) as 'Cost_Administration_Finance__c',
    ISNULL(Min(Case when DEPARTMENTNAME = 'Operations' then AMOUNT  end), 0.00) as 'CostOperations_Site__c',
    ISNULL(Min(Case when DEPARTMENTNAME NOT In ('Operations,Account Managers, Cost_Administration_Information_Techno__c, Data,Information Technology Managers,Operations-Office,Project Mgmt,Recruiting-Coordinators,Recruiting-Callers,Relationship Manager,Sales,Finance') then AMOUNT  end), 0.00) as 'CostOperations_Site__c',
    JOBNAME AS TimeForce_Job__c
    
    FROM (SELECT SUM(Amount) as Amount, 
                 DepartmentName, 
                 JobName
                    FROM (SELECT 
                                Job.jobname, 
                                Job.jobNumber,
                                SUM(total_hr) as TotalHours,  
                                Amount = ROUND(SUM(total_hr) *  Task.BillRate,2),
                                DepartmentName,
                                Task.billrate    
                                            
                                FROM timeCard 
                                INNER JOIN Job 
                                    ON timecard.job_id = Job.job_id  
                                INNER JOIN tblDepartment
                                    ON timeCard.DEPARTMENT_ID = tblDepartment.DEPARTMENT_ID    
                                LEFT OUTER JOIN Task
                                    ON timeCard.task_id = Task.task_id  
                                INNER JOIN empMain 
                                    ON timecard.employee_id = empMain.employee_id
                                
                                Where Job.complete_yn = 0 and 
                                      Task.BillRate > 0 and
                                      Job.jobname != 'None'
                                      
                                       
                                GROUP BY DepartmentName, 
                                         Job.jobNumber,
                                         Job.jobname,
                                         Task.billrate) AS Sub
            <cfif isdefined("arguments.jobList")>
                and jobName in (<cfqueryparam list="yes" value="#arguments.joblist#" cfsqltype="cf_sql_varchar">)
            </cfif>    
            group by JobName,
                     DepartmentName) AS Totals
    group by jobName