Amendment to Results table

[Halliarse]

Hi

My knowledge of coding stored procedures is somewhat limited and I'm struggling with this one (A lot!!)

The following code creates a results table grouped by Cost centre and then 4 fields of values calculated and updated. I need to change the procedure so that the first group field in the results table is the JobID from the JobOps table and then the division and cost centre details, followed by the calculated data and I'm not sure where to start! Any help would be greatly appreciated.

Thanks

Steve

USE [KnibbsPBML]
GO
/****** Object: StoredProcedure [dbo].[rptHCC] Script Date: 17/06/2015 18:29:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[rptHCC]
(
@FromDate AS DATETIME,
@ToDate AS DATETIME
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY

-- Create a Results Table
DECLARE @Results TABLE (
DisplayOrder INT,
Division NVARCHAR(50),
CostCentre NVARCHAR(50),
TargetSet INT,
TargetRun INT,
ActualSet INT,
ActualRun INT

)


-- Create a table listing Job in the period
DECLARE @JobList TABLE(
JobId 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
INSERT INTO @JobList (JobId)
SELECT DISTINCT J.JobId
FROM JobDelivery JD

JOIN Job J
ON JD.JobId = J.JobId

WHERE JD.SQLDelDate >= @FromDate
AND JD.SQLDelDate < @ToDate

-- Ignore Rework Jobs
AND (Rework IS NULL OR Rework <> 'REWORK')

-- Fill the results table with a list of cost centres with their divisions
INSERT INTO @Results (
DisplayOrder,
Division,
CostCentre,
TargetSet,
TargetRun,
ActualSet,
ActualRun
)
SELECT
D.DisplayOrder,
D.Description,
CC.CCDesc,
0,0,0,0

FROM CostCentre CC WITH (NOLOCK)

JOIN Divisions D WITH (NOLOCK)
ON CC.CCDiv = D.CCDiv

-- Update TargetSet
UPDATE Results
SET TargetSet = DOT.TargetSet
FROM @Results Results

JOIN (


SELECT D.Description Division,
Costcentre,
SUM(SetupMins) TargetSet

FROM @JobList JL

JOIN JobOps WITH (NOLOCK)
ON JL.JobId = JobOps.JobId

JOIN CostCentre CC WITH (NOLOCK)
ON JobOps.CostCentreId = cc.ID

JOIN Divisions D WITH (NOLOCK)
ON CC.CCDiv = D.CCDiv

GROUP BY D.Description, CostCentre


) DOT

ON Results.Division = DOT.Division
AND Results.CostCentre = DOT.CostCentre

-- Update TargetRun
UPDATE Results
SET TargetRun = DOT.TargetRun
FROM @Results Results

JOIN (


SELECT D.Description Division,
Costcentre,

-- Target Run is per item so multiply by the Job Qty
SUM(RunMins * J.Qty) TargetRun

FROM @JobList JL

JOIN Job J
ON JL.JobId = J.JobId

JOIN JobOps WITH (NOLOCK)
ON JL.JobId = JobOps.JobId

JOIN CostCentre CC WITH (NOLOCK)
ON JobOps.CostCentreId = CC.ID

JOIN Divisions D WITH (NOLOCK)
ON CC.CCDiv = D.CCDiv

GROUP BY D.Description, CostCentre


) DOT

ON Results.Division = DOT.Division
AND Results.CostCentre = DOT.CostCentre

-- Update ActualSet
UPDATE Results
SET ActualSet = DOT.ActualSet
FROM @Results Results

JOIN (


SELECT D.Description Division,
Costcentre,
SUM(ActSetupMins) ActualSet

FROM @JobList JL

JOIN JobOps WITH (NOLOCK)
ON JL.JobId = JobOps.JobId

JOIN CostCentre CC WITH (NOLOCK)
ON JobOps.CostCentreId = CC.ID

JOIN Divisions D WITH (NOLOCK)
ON CC.CCDiv = D.CCDiv

GROUP BY D.Description, CostCentre


) DOT

ON Results.Division = DOT.Division
AND Results.CostCentre = DOT.CostCentre

-- Update ActualRun
UPDATE Results
SET ActualRun = DOT.ActualRun
FROM @Results Results

JOIN (


SELECT D.Description Division,
Costcentre,

-- ActualRunMins is the total run time for all items (so don't multiply by Job Qty)
SUM(ActRunMins) ActualRun

FROM @JobList JL

JOIN JobOps WITH (NOLOCK)
ON JL.JobId = JobOps.JobId

JOIN CostCentre CC WITH (NOLOCK)
ON JobOps.CostCentreId = CC.ID

JOIN Divisions D WITH (NOLOCK)
ON CC.CCDiv = D.CCDiv

GROUP BY D.Description, CostCentre


) DOT

ON Results.Division = DOT.Division
AND Results.CostCentre = DOT.CostCentre


-- Display the results
SELECT
Division,
CostCentre,
TargetSet,
TargetRun,
ActualSet,
ActualRun

FROM @Results
ORDER BY DisplayOrder

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