Code:
SELECT
Fiscal_calendar.Fiscal_Year, Fiscal_calendar.Quarter, Fiscal_calendar.Period, Fiscal_calendar.Week,
[00 Inbound productivity].ProdDate,
[00 Inbound productivity].shift,
[SumOfUnits for Broken case receiving]/[SumOfOffloading_Hrs] AS BrkCaseRec_UPH,
[SumOfUnits for case receiving]/[SumOfHrs for case receiving] AS CaseRec_UPH,
[SumOfUnits for Pallet level receiving]/[SumOfHrs for Pallet level receiving] AS PalRec_UPH,
[SumOfTasks processed]/([SumOfHrs for case receiving]+[SumOfHrs for Pallet level receiving]) AS Rec_TasksPerHour,
[SumOfBRK_Units_Putaway]/[SumOfBC_Put_Hours] AS BRKPA_UPH,
[SumOfBRK_Ctns_Putaway]/[SumOfBC_Put_Hours] AS BRKPA_CPH,
[SumOfBRK_Putaway_WTS]/[SumOfBC_Put_Hours] AS BRKPA_TasksPH,
[SumOfCST_units_putaway]/[SumOfCST_Put_Hours] AS CasePA_UPH,
[SumOfCST_Ctns_putaway]/[SumOfCST_Put_Hours] AS CasePA_CPH,
[SumOfCST_Putaway_WTS]/[SumOfCST_Put_Hours] AS CasePA_TaskPH,
[SumOfPST_units_Putaway]/[SumOfPST_Put_Hours] AS PalletPA_UPH,
[SumOfPST_Ctns_Putaway]/[SumOfPST_Put_Hours] AS PalletPA_CPH,
[SumOfPST_Putaway_WTS]/[SumOfPST_Put_Hours] AS PalletPA_TasksPH,
Sum([00 Inbound productivity].[Units for Broken case receiving]) AS [SumOfUnits for Broken case receiving],
Sum([00 Inbound productivity].[Units for case receiving]) AS [SumOfUnits for case receiving],
Sum([00 Hours].[Hrs for case receiving]) AS [SumOfHrs for case receiving],
Sum([00 Inbound productivity].[Units for Pallet level receiving]) AS [SumOfUnits for Pallet level receiving],
Sum([00 Hours].[Hrs for Pallet level receiving]) AS [SumOfHrs for Pallet level receiving],
Sum([00 Inbound productivity].[Tasks processed]) AS [SumOfTasks processed],
Sum([00 Inbound productivity].BRK_Units_Putaway) AS SumOfBRK_Units_Putaway,
Sum([00 Inbound productivity].BRK_Ctns_Putaway) AS SumOfBRK_Ctns_Putaway,
Sum([00 Inbound productivity].BRK_Putaway_WTS) AS SumOfBRK_Putaway_WTS,
Sum([00 Hours].BC_Put_Hours) AS SumOfBC_Put_Hours,
Sum([00 Inbound productivity].CST_units_putaway) AS SumOfCST_units_putaway,
Sum([00 Inbound productivity].CST_Ctns_putaway) AS SumOfCST_Ctns_putaway,
Sum([00 Inbound productivity].CST_Putaway_WTS) AS SumOfCST_Putaway_WTS,
Sum([00 Hours].CST_Put_Hours) AS SumOfCST_Put_Hours,
Sum([00 Inbound productivity].PST_units_Putaway) AS SumOfPST_units_Putaway,
Sum([00 Inbound productivity].PST_Ctns_Putaway) AS SumOfPST_Ctns_Putaway,
Sum([00 Inbound productivity].PST_Putaway_WTS) AS SumOfPST_Putaway_WTS,
Sum([00 Hours].PST_Put_Hours) AS SumOfPST_Put_Hours,
Sum([00 Inbound productivity].[Stock move cases]) AS [SumOfStock move cases],
Sum([00 Hours].[Stock Move Hours]) AS [SumOfStock Move Hours],
Sum([00 Inbound productivity].[Consolidation Tasks]) AS [SumOfConsolidation Tasks],
Sum([00 Hours].[Consolidation Hours]) AS [SumOfConsolidation Hours],
Sum([00 Hours].Offloading_Hrs) AS SumOfOffloading_Hrs,
Sum([00 Hours].Staging_Hrs) AS SumOfStaging_Hrs,
Sum([00 Hours].Redirect_Hrs) AS SumOfRedirect_Hrs,
Sum([00 Hours].Bargain_rec_Hrs) AS SumOfBargain_rec_Hrs,
Sum([00 Hours].[Meeting Hrs]) AS [SumOfMeeting Hrs],
Sum([00 Hours].[Training Hrs]) AS [SumOfTraining Hrs],
Sum([00 Hours].[Problem solver Hrs]) AS [SumOfProblem solver Hrs],
Sum([00 Hours].[Hourly Team leads Hrs]) AS [SumOfHourly Team leads Hrs],
Sum([00 Hours].[Clerical Hrs]) AS [SumOfClerical Hrs],
Sum([00 Hours].[Sick Hours]) AS [SumOfSick Hours],
Sum([00 Hours].[Vacation Hrs]) AS [SumOfVacation Hrs],
Sum([00 Hours].[Loan out Hrs]) AS [SumOfLoan out Hrs],
Sum([00 Hours].[Borrowed Hrs]) AS [SumOfBorrowed Hrs],
[SumOfHrs for case receiving]+[SumOfHrs for Pallet level receiving]+[SumOfBC_Put_Hours]+[SumOfCST_Put_Hours]+[SumOfPST_Put_Hours]+[SumOfOffloading_Hrs]+[SumOfStaging_Hrs]+[SumOfRedirect_Hrs]+[SumOfBargain_rec_Hrs]+[SumOfConsolidation Hours]+[SumOfStock Move Hours] AS [Direct Hours],
[SumOfMeeting Hrs]+[SumOfTraining Hrs]+[SumOfHourly Team leads Hrs]+[SumOfClerical Hrs]+[SumOfProblem solver Hrs] AS [Indirect Hours],
Sum([00 Hours].[Birthday hours]) AS [SumOfBirthday hours],
Sum([00 Hours].[DC Day Hours]) AS [SumOfDC Day Hours],
Sum([00 Hours].[Bereavement hours]) AS [SumOfBereavement hours],
Sum([00 Hours].[Jury duty Hours]) AS [SumOfJury duty Hours],
Sum([00 Hours].[Unpaid time off Hours]) AS [SumOfUnpaid time off Hours],
[SumOfConsolidation Tasks]/[SumOfConsolidation Hours] AS [Consolidation TPH],
[SumOfStock move cases]/[SumOfStock Move Hours] AS [Movement TPH]
FROM (Fiscal_calendar INNER JOIN [00 Hours] ON Fiscal_calendar.Date = [00 Hours].ProdDate)
LEFT JOIN [00 Inbound productivity] ON ([00 Hours].userID = [00 Inbound productivity].userID) AND ([00 Hours].shift = [00 Inbound productivity].shift) AND ([00 Hours].ProdDate = [00 Inbound productivity].ProdDate)
GROUP BY Fiscal_calendar.Fiscal_Year, Fiscal_calendar.Quarter, Fiscal_calendar.Period, Fiscal_calendar.Week, [00 Inbound productivity].ProdDate, [00 Inbound productivity].shift
HAVING ((([00 Inbound productivity].ProdDate)=[Input Date]) AND (([00 Inbound productivity].shift)=[Input Shift]))
ORDER BY Fiscal_calendar.Fiscal_Year DESC , Fiscal_calendar.Quarter DESC , Fiscal_calendar.Period DESC , Fiscal_calendar.Week DESC , [00 Inbound productivity].ProdDate DESC , [00 Inbound productivity].shift DESC;