troubleshooting Question

Combine tables to get all results whether employee is in one table or the other

Avatar of HSI_guelph
HSI_guelphFlag for Canada asked on
SSRSMicrosoft SQL Server 2008
11 Comments1 Solution282 ViewsLast Modified:
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.

Report Table
Employees in the respective tables
Results of each table
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
FROM         WIP
WHERE     (WCltID = 630)
UNION
SELECT     CBudempID, CBudEmpLName
FROM         CltBudget
WHERE     (CBudCltID = 630)

returns
Results of new query
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!!!

Old code
SELECT DISTINCT 
                      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,
                          (SELECT     Empfname
                            FROM          Employee
                            WHERE      (ID = WIP.WempID)) AS EmpFName, WEmpLName AS EmpLName, ISNULL
                          ((SELECT     SUM(ISNULL(CBudhours, 0)) AS hours
                              FROM         CltBudget
                              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
                              FROM         CltDueCustom
                              WHERE     (CDCustId = 22) AND (CDCustCltId = WIP.WCltID)), 0) AS OriginalBudgetedLabour
FROM         WIP
WHERE     (ISNULL(Windicator, 0) <> 'D') AND (WCltID = @Client_ID)
GROUP BY WCodeCat, WCodeSub, WCltID, WCltName, WCltNum, WEng, WempID, WEmpLName
HAVING      (WCltID = @Client_ID)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros