-- Actual SQL to go into Yellowfin! SELECT --Filter Fields tot.date_from as [Timesheet Period] --Main Report Fields ,c.forename + ' ' + c.surname AS [Contact Name] , CASE WHEN o.Branch_Name IS NULL OR o.Branch_Name='' THEN o.Name ELSE o.Name + ' (' + o.Branch_Name + ') ' END AS Organisation , isnull(d.name, '') AS Department , isnull(g.name, '') AS Grade , sum(tot.ProjectTime) [Project Time] , sum(tot.OverheadTime) [Overhead Time] , sum(tot.LeaveTime) [Leave Time] , sum(tot.ProjectTime + tot.OverheadTime) AS [Recorded Time] , CASE WHEN sum(tot.ProjectTime + tot.OverheadTime) > 0 THEN sum((tot.projecttime) * 100) / sum(tot.ProjectTime + tot.OverheadTime) ELSE 0 END AS [% Productive Ex. Leave] , CASE WHEN sum(tot.ProjectTime + tot.OverheadTime + tot.leavetime) > 0 THEN sum((tot.projecttime) * 100) / sum(tot.ProjectTime + tot.OverheadTime + tot.leavetime) ELSE 0 END AS [% Productive Inc. Leave] , AU.Approved_Utilisation as [% Approved Utilisation] , CASE WHEN ISNULL(AU.Approved_Utilisation, 0) > CASE WHEN sum(tot.ProjectTime + tot.OverheadTime) > 0 THEN sum((tot.projecttime) * 100) / sum(tot.ProjectTime + tot.OverheadTime) ELSE 0 END THEN 'Utilisation Below Approved' ELSE '' END AS [Utilisation Check] FROM contact C LEFT JOIN ( SELECT a.Contact_ID , sum(isnull(a.Chargeable, 0)) ProjectTime , sum(isnull(a.Overhead, 0)) OverheadTime , sum(isnull(a.Leave, 0)) LeaveTime , FP.Date_From FROM timesheet_period FP LEFT JOIN ( SELECT t.Contact_ID , fp.date_from , CASE WHEN isnull(tic.Non_Entity, 0) = 0 And TIO.NonOverhead = 1 THEN sum(isnull(tit.time_basic, 0)) + sum(isnull(tit.Time_Overtime, 0)) ELSE 0 END AS Chargeable , CASE WHEN ((isnull(tic.Non_Entity, 1) = 1 AND isnull(Is_Leave, 0) = 0) OR TIO.NonOverhead = 0) THEN sum(isnull(tit.time_basic, 0)) + sum(isnull(tit.Time_Overtime, 0)) ELSE 0 END AS Overhead , CASE WHEN isnull(tic.Non_Entity, 0) = 1 AND isnull(Is_Leave, 0) = 1 THEN sum(isnull(tit.time_basic, 0)) + sum(isnull(tit.Time_Overtime, 0)) ELSE 0 END AS Leave FROM timesheet T LEFT JOIN timesheet_item TI ON t.Timesheet_ID = ti.Timesheet_ID LEFT JOIN timesheet_item_time TIT ON tit.Timesheet_Item_ID = ti.Timesheet_Item_ID LEFT JOIN Timesheet_Item_Category TIC ON tic.Timesheet_Item_Category_ID = ti.Timesheet_Item_Category_ID LEFT JOIN timesheet_period FP ON t.timesheet_period_id = FP.timesheet_period_id LEFT JOIN (select ti.Timesheet_Item_ID, isnull(v.project_ID, p.Project_ID) as Project_ID, Case when o.Project_ID is not null then 0 else 1 end as NonOverhead from Timesheet_Item ti Left Join Project p on p.Project_ID = ti.Entity_Identifier and ti.Entity_Class_ID = 3 Left Join Variation v on v.Variation_ID = ti.Entity_Identifier and ti.Entity_Class_ID = 8 Left Join (Select Project_ID from Project where project_code like '%-99') o on o.Project_ID = isnull(v.project_ID, p.Project_ID) ) TIO ON TIO.Timesheet_Item_ID = TI.Timesheet_Item_ID -- u2Comment:13 May 2016:James Lawrence:Exclude 9 day fortnight category WHERE ti.Timesheet_Item_Category_ID <> 55 -- WHERE t.approved = 1 GROUP BY fp.date_from , t.Contact_ID , tic.Non_Entity , tic.Is_Leave , TIO.NonOverhead ) A ON a.Date_from = fp.Date_From GROUP BY a.Contact_ID , FP.Date_From ) TOT ON tot.Contact_ID = c.Contact_ID LEFT JOIN organisation O ON o.Organisation_ID = c.Organisation_ID LEFT JOIN department D ON d.Department_ID = c.Department_ID LEFT JOIN grade G ON g.Grade_ID = c.Grade_ID LEFT JOIN (Select c.Contact_ID, ISNULL(c.Utilisation_Percentage, g.Utilisation_Percentage) as Approved_Utilisation from Contact c left join Timesheet_Contact_Parameter tcp on tcp.Contact_ID = c.Contact_ID left join Grade g on g.Grade_ID = c.Grade_ID) AU on AU.Contact_ID = c.Contact_ID GROUP BY --Filters tot.date_from --Main Report Fields , c.surname , c.forename , d.name , g.name , o.name , o.branch_name , AU.Approved_Utilisation HAVING sum(tot.ProjectTime) > 0 OR sum(tot.OverheadTime) > 0 OR sum(tot.LeaveTime) > 0 ORDER BY c.forename , c.surname