/****** Object: StoredProcedure [dbo].[rptJobsByDivision] Script Date: 08/13/2018 20:54:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[rptJobsByDivision] ( @FromDate AS DATETIME, @ToDate AS DATETIME, @CustomerId AS INT = NULL ) AS BEGIN SET NOCOUNT ON BEGIN TRY -- Create a Results Table DECLARE @Results TABLE ( JobId INT, [Description] NVARCHAR(50), DrawingNo NVARCHAR(50), Qty INT, BTARevenue FLOAT, BTATime INT, DRILLRevenue FLOAT, DRILLTime INT, GRDRevenue FLOAT, GRDTime INT, HONERevenue FLOAT, HONETime INT, LOGRevenue FLOAT, LOGTime INT, MATRevenue FLOAT, MATTime INT, QARevenue FLOAT, QATime INT, SCRevenue FLOAT, SCTime INT, SGARevenue FLOAT, SGATime INT, UNIT1Revenue FLOAT, UNIT1Time INT ) -- To Date is passed as a short date. Add one date so that information on that day is included SELECT @ToDate = DATEADD(d, 1, @ToDate) -- Get a List of Jobs to Analyse INSERT INTO @Results ( JobId, [Description], DrawingNo, Qty, BTARevenue, BTATime, DRILLRevenue, DRILLTime, GRDRevenue, GRDTime, HONERevenue, HONETime, LOGRevenue, LOGTime, MATRevenue, MATTime, QARevenue, QATime, SCRevenue, SCTime, SGARevenue, SGATime, UNIT1Revenue, UNIT1Time ) SELECT JobId, JobDesc, DrawingNo + '-' + DrawingIssue, Job.Qty, 0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0 FROM Job WHERE Job.Complete = 1 AND SQLCompletedDate >= @FromDate AND SQLCompletedDate <@ToDate AND ( (@CustomerId IS NULL) OR (CustId = @CustomerId) ) ORDER BY CustId, DrawingNo, JobId -- UNIT1 UPDATE Results SET UNIT1Revenue = subQuery.Revenue, UNIT1Time = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'UNIT1' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- SGA UPDATE Results SET SGARevenue = subQuery.Revenue, SGATime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'SGA' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- SC UPDATE Results SET SCRevenue = subQuery.Revenue, SCTime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'SC' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- QA UPDATE Results SET QARevenue = subQuery.Revenue, QATime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'QA' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- MAT UPDATE Results SET MATRevenue = subQuery.Revenue, MATTime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'MAT' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- LOG UPDATE Results SET LOGRevenue = subQuery.Revenue, LOGTime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'LOG' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- HONE UPDATE Results SET HONERevenue = subQuery.Revenue, HONETime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'HONE' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- GRD UPDATE Results SET GRDRevenue = subQuery.Revenue, GRDTime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'GRD' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- DRILL UPDATE Results SET DRILLRevenue = subQuery.Revenue, DRILLTime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'DRILL' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- BTA UPDATE Results SET BTARevenue = subQuery.Revenue, BTATime = subQuery.DivTime FROM @Results Results JOIN ( SELECT J.JobId, D.Description Division, SUM(OPUnitCost * J.Qty) Revenue, SUM(COALESCE(ActSetupMins, 0) + COALESCE(ActRunMins, 0)) DivTime FROM JobOps JO WITH (NOLOCK) JOIN Job J WITH (NOLOCK) ON JO.JobId = J.JobId JOIN JobDelivery JD WITH (NOLOCK) ON J.JobId = JD.JobId JOIN CostCentre CC WITH (NOLOCK) ON JO.CostCentreId = CC.ID JOIN Divisions D WITH (NOLOCK) ON CC.CCDiv = D.CCDiv WHERE D.CCDiv = 'BTA' GROUP BY J.JobId, D.Description ) subQuery ON subQuery.JobId = Results.JobId -- Display the results SELECT JobId, [Description], DrawingNo, Qty, BTARevenue, BTATime, DRILLRevenue, DRILLTime, GRDRevenue, GRDTime, HONERevenue, HONETime, LOGRevenue, LOGTime, MATRevenue, MATTime, QARevenue, QATime, SCRevenue, SCTime, SGARevenue, SGATime, UNIT1Revenue, UNIT1Time FROM @Results ORDER BY DrawingNo, JobId END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) BEGIN ROLLBACK END DECLARE @Errmsg nvarchar (4000), @ErrSeverity int SELECT @Errmsg=ERROR_MESSAGE (), @ErrSeverity=ERROR_SEVERITY () RAISERROR (@Errmsg, @ErrSeverity, 1) END CATCH END