Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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

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)

Open in new window


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)

Open in new window

0
HSI_guelph
Asked:
HSI_guelph
1 Solution
 
nemws1Commented:
You can always use a fake field to get things to match when using UNION.  For example, say you want the WEmpBirthMonth from WIP, which is an integer.  Below, I make the columns match by just putting in a static '0' in the second select.

SELECT     WempID, WEmpLName, WEmpBirthMonth
FROM         WIP
WHERE     (WCltID = 630)
UNION
SELECT     CBudempID, CBudEmpLName, 0
FROM         CltBudget
WHERE     (CBudCltID = 630)

Open in new window

0
 
Scott PletcherSenior DBACommented:
SQL provides a way to do this: a FULL OUTER JOIN.  Like so:


SELECT
    COALESCE(wip.WEmpID, cb.WEmpID) AS EmpID,
    SUM(cb.CBudhours) AS [Budgeted Hours],
    SUM(wip.Whours) AS [Actual Hours],
    SUM(cb.CBudhours) - SUM(wip.Whours) AS Variance
    --,...<cost columns similar to hours columns above>
FROM WIP wip
FULL OUTER JOIN CltBudget cb ON
    cb.EmpID = wip.WEmpID
WHERE
    wip.WCltID = 630 AND
    cb.CBudCltID = 630
GROUP BY
    COALESCE(wip.WEmpID, cb.WEmpID)
0
 
HSI_guelphAuthor Commented:
Sweet!  That's awesome!  But now I sometimes get doubles.  I think I don't fully understand how the Union works.  I think what I'd like to do is take a row from one table (unique CltID and EmpID) and combine it with a row from the second table (same CltID and EmpID as the first table) and return one single row.  Most of the same information can be found in each table (client name, codes, employee names, etc) but the unique fields I need are the hours/fee in the WIP table for what has been entered and the hours from the CltBudget table that the Project Manager budgeted that employee for (if there are any).

New results with the suggested change
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
PortletPaulCommented:
>>"That's awesome!  But now I sometimes get doubles. "

UNION produces "unique rows", so every row in the result is different to all others rows.
Each and every column is considered when determining a "unique row" look at just one double, you will see there is in fact a difference, and that difference will be present for each "double"unique rows
You have an expectation that is "wrong" for this technique, UNION does not understand you only want one row per "the first column", it just doesn't work that way. When you introduced the third column (which is always different in the upper query to the value in the lower query) you doomed the UNION to produce 2 rows for every WEPMID that is found in both.

Put simply: that 3rd column is the cause of "doubles"
0
 
PortletPaulCommented:
Instead of adding more columns to the union, do the reverse, get just the wanted unique column(s) and then join back to a table to the balance of the information (such as names). e.g.
SELECT
      emps.WempID
    , employee.LastName
    , employee.DOB
FROM (
            SELECT
                  WempID
                                     --, WEmpLName, WEmpBirthMonth
            FROM WIP
            WHERE (WCltID = 630)
            UNION
                  SELECT
                        CBudempID
                                     --, CBudEmpLName, 0
                  FROM CltBudget
                  WHERE (CBudCltID = 630)
      ) AS emps
      INNER JOIN employee
                  ON emps.WempID = employee.id

Open in new window

Although I recommend you try the full outer join approach as proposed by Scott Pletcher,
0
 
HSI_guelphAuthor Commented:
@Scott - I tried your code but it only returned 3 results.
Results of Coalesce
0
 
PortletPaulCommented:
try an OR on the where clause, see line 10:
SELECT
      COALESCE(wip.WEmpID, cb.WEmpID)     AS EmpID
    , SUM(cb.CBudhours)                   AS [Budgeted Hours]
    , SUM(wip.Whours)                     AS [Actual Hours]
    , SUM(cb.CBudhours) - SUM(wip.Whours) AS Variance
FROM WIP wip
      FULL OUTER JOIN CltBudget cb
                  ON cb.EmpID = wip.WEmpID
WHERE wip.WCltID = 630
      OR cb.CBudCltID = 630 --<< change is here
GROUP BY
      COALESCE(wip.WEmpID, cb.WEmpID)

Open in new window

0
 
HSI_guelphAuthor Commented:
@PortletPaul - That works great!  Now I need to grab the sum of hours from the WIP table and the CltBudget table.  I'll play around with it but if you have a suggestion on where to put that I'd appreciate it!!

New results
0
 
PortletPaulCommented:
did you try that small change to the full outer join? this may be all you need to do, change "and" to "or"
0
 
PortletPaulCommented:
but if you still want a "union" approach, perhaps this:
SELECT
      emps.WempID
    , employee.LastName
    , employee.DOB
    , emps.Actual_hrs
    , emps.Variance
FROM (
            SELECT
                  WempID
                , SUM(Whours)                 AS Actual_hrs
                , SUM(Budhours) - SUM(Whours) AS Variance
            FROM (
                        SELECT
                              WempID
                            , Whours
                            , 0 AS CBudhours
                        FROM WIP
                        WHERE (WCltID = 630)
                        UNION ALL
                              SELECT
                                    CBudempID
                                  , 0
                                  , CBudhours
                              FROM CltBudget
                              WHERE (CBudCltID = 630)
                  ) AS sq
            GROUP BY
                  WempID
      ) AS emps
      INNER JOIN employee
                  ON emps.WempID = employee.id

Open in new window

0
 
HSI_guelphAuthor Commented:
That small change did the trick!  Thank you so much for your help!  Thank you everyone for posting!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now