SELECT T_Schedule.ID, T_Schedule.Dim_Version_ID, T_Schedule.Dim_Activity_ID, T_Schedule.Dim_Resource_ID, T_Schedule.Start, T_Schedule.Finish, T_Schedule.Task_Duration, IIf(((T_Schedule.Start_Work_Hour<=T_Calendar.End_Work_Hour) And (T_Schedule.End_Work_Hour>=T_Calendar.Start_Work_Hour)),(IIf(T_Schedule.End_Work_Hour>=T_Calendar.End_Work_Hour,T_Calendar.End_Work_Hour,T_Schedule.End_Work_Hour)-IIf(T_Schedule.Start_Work_Hour<=T_Calendar.Start_Work_Hour,T_Calendar.Start_Work_Hour,T_Schedule.Start_Work_Hour))/T_Schedule.Work_Hour_Duration,0) AS Duration_Percent, IIf([Rate_Basis]='Task',[Duration_Percent],[Task_Duration]*[Duration_Percent]) AS AdjHours, IIf(((T_Schedule.Start_Work_Hour<=T_Calendar.End_Work_Hour) And (T_Schedule.End_Work_Hour>=T_Calendar.Start_Work_Hour)),[AdjHours]/(IIf(T_Schedule.End_Work_Hour>=T_Calendar.End_Work_Hour,T_Calendar.End_Work_Hour,T_Schedule.End_Work_Hour)-IIf(T_Schedule.Start_Work_Hour<=T_Calendar.Start_Work_Hour,T_Calendar.Start_Work_Hour,T_Schedule.Start_Work_Hour)),0) AS Utilisation, T_Calendar.Week, T_Calendar.Period, T_Calendar.Year, T_Activity.Activity_ID, T_Activity.Activity_Name, T_Activity.WBS_Code, T_Activity.WBS_Name, T_Activity.Package, T_Activity.Phase, T_Activity.Work_Type, T_Activity.Design_Category, T_Activity.Activity_Type, T_Activity.Task, T_Activity.Work_Order_Type, T_Activity.Agresso_Project_Code, T_Activity.Agresso_Package_Code, T_Resources.Role_Resource_Key, T_Resources.Resource_Name, T_Resources.PID, T_Resources.Role_Name, T_Resources.Grade, T_Resources.BG_Code, T_Resources.Skill_Group_Code, T_Resources.Skill_Group, T_Resources.Country_ID, T_Resources.Country_Code, T_Resources.Base_office_currency_code, T_Resources.Office_Code, T_Resources.Office, T_Resources.Location_Type, T_Resources.Rate_Basis, T_Resources.Overhead_PRC_Ratio, T_Resources.Expenses_PRC_Ratio, T_Resources.Coordination_PRC_Ratio, T_Resources.Base_office_Project_X_Rate, T_Resources.Base_office_GBP_X_Rate, T_Resources.Base_office_Currency_PRC_Rate, T_Resources.Project_Currency_PRC_Rate, T_Resources.GBP_PRC_Rate, IIf([Location_Type]='Sub-contract',1,[Inflation_Rate]) AS Adj_Inflation_Rate, Round([AdjHours]*[Base_office_Currency_PRC_Rate]*[Adj_Inflation_Rate],2) AS BOC_PRC_Cost, Round([AdjHours]*[Base_office_Currency_Overheads_Rate]*[Adj_Inflation_Rate],2) AS BOC_Overheads_Cost, Round([AdjHours]*[Base_office_Currency_Expenses_Rate]*[Adj_Inflation_Rate],2) AS BOC_Expenses_Cost, Round([AdjHours]*[Base_office_Currency_Coordination_Cost_Rate]*[Adj_Inflation_Rate],2) AS BOC_Coordination_Cost, Round([AdjHours]*[Base_office_Currency_Total_Rate]*[Adj_Inflation_Rate],2) AS BOC_Total_Cost, Round([AdjHours]*[Project_Currency_PRC_Rate]*[Adj_Inflation_Rate],2) AS Project_PRC_Cost, Round([AdjHours]*[Project_Currency_Overheads_Rate]*[Adj_Inflation_Rate],2) AS Project_Overheads_Cost, Round([AdjHours]*[Project_Currency_Expenses_Rate]*[Adj_Inflation_Rate],2) AS Project_Expenses_Cost, Round([AdjHours]*[Project_Currency_Coordination_Cost_Rate]*[Adj_Inflation_Rate],2) AS Project_Coordination_Cost, Round([AdjHours]*[Project_Currency_Total_Rate]*[Adj_Inflation_Rate],2) AS Project_Total_Cost, Round([AdjHours]*[GBP_PRC_Rate]*[Adj_Inflation_Rate],2) AS GBP_PRC_Cost, Round([AdjHours]*[GBP_Overheads_Rate]*[Adj_Inflation_Rate],2) AS GBP_Overheads_Cost, Round([AdjHours]*[GBP_Expenses_Rate]*[Adj_Inflation_Rate],2) AS GBP_Expenses_Cost, Round([AdjHours]*[GBP_Coordination_Cost_Rate]*[Adj_Inflation_Rate],2) AS GBP_Coordination_Cost, Round([AdjHours]*[GBP_Total_Rate]*[Adj_Inflation_Rate],2) AS GBP_Total_Cost
FROM (((T_Calendar INNER JOIN T_Schedule ON (T_Calendar.Country_Code = T_Schedule.Country_Code) AND (T_Calendar.Dim_Version_ID = T_Schedule.Dim_Version_ID)) INNER JOIN T_Resources ON T_Schedule.Dim_Resource_ID = T_Resources.Dim_Resource_ID) INNER JOIN T_Activity ON T_Schedule.Dim_Activity_ID = T_Activity.Dim_Activity_ID) INNER JOIN T_InflationRates ON (T_Calendar.Year = T_InflationRates.Year) AND (T_Calendar.Country_Code = T_InflationRates.Country_Code) AND (T_Calendar.Dim_Version_ID = T_InflationRates.Dim_Version_ID)
WHERE (((IIf((([T_Schedule].[Start_Work_Hour]<=[T_Calendar].[End_Work_Hour]) And ([T_Schedule].[End_Work_Hour]>=[T_Calendar].[Start_Work_Hour])),(IIf([T_Schedule].[End_Work_Hour]>=[T_Calendar].[End_Work_Hour],[T_Calendar].[End_Work_Hour],[T_Schedule].[End_Work_Hour])-IIf([T_Schedule].[Start_Work_Hour]<=[T_Calendar].[Start_Work_Hour],[T_Calendar].[Start_Work_Hour],[T_Schedule].[Start_Work_Hour]))/[T_Schedule].[Work_Hour_Duration],0))>0))
ORDER BY T_Schedule.Dim_Version_ID, T_Schedule.Dim_Activity_ID, T_Schedule.Dim_Resource_ID, T_Calendar.Week;