I am running the following query trying to get:
One line per Project Manager with a sum of all contracts assigned to that PM. This query is just a test so I'm not summing up anything.
What I am trying to do is get one record per contract.
The conundrum: The job master has the software assigned PM on one tab. Some custom fields have been added to another tab. There are 5 other custom PM fields added to the job master. I have to check the software version PM and all 5 custom fields. If the current PM exists in any of those fields I need to pick up the job. However - if the PM is on the main field AND in one of the custom fields I need to get only one coming back or it will skew my contract numbers.
SELECT dbo.JCCM.JCCo, dbo.JCCM.Contract, JCJM.Job, dbo.JCCM.OrigContractAmt, dbo.JCCM.ContractAmt, dbo.JCCM.udPlanFee2,
dbo.JCCM.udPlanFee, dbo.JCJM.ProjectMgr AS JOBPM, dbo.JCMP.ProjectMgr AS MPPM, dbo.JCJM.udPM1,
dbo.JCJM.udPM2, dbo.JCJM.udPM3, dbo.JCJM.udPM4, dbo.JCJM.udPM5, zMCG_JCEmpPMConversion_1.Employee,
FROM dbo.JCCM INNER JOIN --Main Contract table
-- Job table ties to contract by company and contract
dbo.JCJM ON dbo.JCCM.JCCo = dbo.JCJM.JCCo AND dbo.JCCM.Contract = JCJM.Job Left outer JOIN
-- Software Project manager table - ties to job by Co and project manager
dbo.JCMP ON dbo.JCCM.JCCo = dbo.JCMP.JCCo AND dbo.JCJM.ProjectMgr = dbo.JCMP.ProjectMgr LEFT OUTER JOIN
-- PMX fields in job master don't use project manager table - it uses the employee number from payroll
-- don't ask me why - It's custom and I didn't write it. The employees also have a project manager record
-- in JCMP This table ties the employee number to the project manager number
-- I am having to check all 5 PMX fields in the job master because they want the total to include a
-- job when they are listed in the custom PMX fields as well as the project manager field
-- in the job master.
dbo.zMCG_JCEmpPMConversion AS zMCG_JCEmpPMConversion_1 ON dbo.JCJM.JCCo = zMCG_JCEmpPMConversion_1.JCCo
and dbo.JCJM.udPM1 = zMCG_JCEmpPMConversion_1.Employee
left outer join dbo.zMCG_JCEmpProgMgrConversion as zMCG_JCEmpPMConversion_2 on JCJM.JCCo
= zMCG_JCEmpPMConversion_2.JCCo and JCJM.udPM2 = zMCG_JCEmpPMConversion_2.Employee
left outer join dbo.zMCG_JCEmpProgMgrConversion as zMCG_JCEmpPMConversion_3 on JCJM.JCCo
= zMCG_JCEmpPMConversion_3.JCCo and JCJM.udPM3 = zMCG_JCEmpPMConversion_3.Employee
left outer join dbo.zMCG_JCEmpProgMgrConversion as zMCG_JCEmpPMConversion_4 on JCJM.JCCo
= zMCG_JCEmpPMConversion_4.JCCo and JCJM.udPM4 = zMCG_JCEmpPMConversion_4.Employee
left outer join dbo.zMCG_JCEmpProgMgrConversion as zMCG_JCEmpPMConversion_5 on JCJM.JCCo
= zMCG_JCEmpPMConversion_5.JCCo and JCJM.udPM5 = zMCG_JCEmpPMConversion_5.Employee
-- Only want the initial job, not sub jobs -- and don't want duplicates of the PM in the software ProjectMgr field because the PM can be listed in -- both places and we only want the contract amount once. where substring(JCJM.Contract,7,4) = ' ' and zMCG_JCEmpPMConversion_1.PMNumber <> JCJM.ProjectMgr and zMCG_JCEmpPMConversion_2.PMNumber <> JCJM.ProjectMgr and zMCG_JCEmpPMConversion_3.PMNumber <> JCJM.ProjectMgr and zMCG_JCEmpPMConversion_4.PMNumber <> JCJM.ProjectMgr and zMCG_JCEmpPMConversion_5.PMNumber <> JCJM.ProjectMgr
I hope someone can help me with this. I've been using SSMS for about 5 years, but never as my main position - just as a supplement to business analyst roles so there is a lot I don't know.