I have a budgets table (CltBudget) and a time entered table (WIP which also has expenses). Ideally every employee who logs hours against a project will also have been budgeted for hours but that is not always the case as some employees are borrowed from other projects. So my report is not returning employees who've entered time against a project but weren't budgeted.
Employees in the respective tables
My current code is below but I feel I should rewrite the code to be cleaner as well as to resolve the current issue so I don't need this code fixed per say. I started a new query using a union which returns the correct number of employees but I can't add other fields unless there is a matching field in the other table.
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
SELECT WempID, WEmpLName
WHERE (WCltID = 630)
SELECT CBudempID, CBudEmpLName
WHERE (CBudCltID = 630)
So I'm wondering if anyone can help me grab all employees whether they are in either table or in both and sum their budgeted hours (CltBudget.CBudhours) and actual hours (WIP.Whours)? Any help would be greatly appreciated!!!
WCltID AS Client_ID, WCltName AS ClientName, WCltNum AS ClientNumber, WEng AS EngagementCode, WCodeCat AS ServiceCategory,
WCodeSub AS ServiceSubcategory, ISNULL(SUM(Whours), 0) AS ActualHours, ISNULL(SUM(Wfee), 0) + ISNULL(SUM(Wwrupdn), 0) AS ActualCost, ISNULL(SUM(Wexp),
0) + ISNULL(SUM(Wwrupdn), 0) AS ActualExpenses, WempID AS WipEmp_ID,
WHERE (ID = WIP.WempID)) AS EmpFName, WEmpLName AS EmpLName, ISNULL
((SELECT SUM(ISNULL(CBudhours, 0)) AS hours
WHERE (CBudCltID = @Client_ID) AND (CBudempID = WIP.WempID)), 0) AS BudgetedHours, ISNULL
((SELECT SUM(ISNULL(CBudfee, 0)) AS fee
FROM CltBudget AS CltBudget_1
WHERE (CBudCltID = @Client_ID) AND (CBudempID = WIP.WempID)), 0) AS BudgetedCost, ISNULL
((SELECT CAST(CDCustValue AS decimal) AS Custom_Value
WHERE (CDCustId = 22) AND (CDCustCltId = WIP.WCltID)), 0) AS OriginalBudgetedLabour
WHERE (ISNULL(Windicator, 0) <> 'D') AND (WCltID = @Client_ID)
GROUP BY WCodeCat, WCodeSub, WCltID, WCltName, WCltNum, WEng, WempID, WEmpLName
HAVING (WCltID = @Client_ID)