Help with outer joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with outer joins

Author

Topic

SBLatta
Starting Member

33 Posts

Posted - 2010-05-10 : 10:42:37

I have the following query, which is kind of confusing. There is a table called PAYCARD_ITEMS, which contains one line for every item on a paycard. The problem is, I need to return both the amount on the current paycard, if any, as well as the YTD total for that payroll category (ex. Overtime). The query below works fine as long as the pay type is on that particular paycard, however, some pay types (like Commissions) may only appear on paycards every once in a while. When the pay type isn't included on a particular pay card, it is being left out of the YTD totals. I had thought that by using an outer join, I could return all of the PAYROLL_CATEGORY pay types, regardless of whether or not they were on a particular pay card in PAYCARD_ITEMS.


SELECT PAYCARDS_COMPLETE.ID_PAYCARD, 
       PAYCARDS_COMPLETE.ID_EMPLOYEE, 
       PAYCARDS_COMPLETE.PAY_DATE, 
       PAYCARDS_COMPLETE.RUN_NUMBER, 
       PAYCARDS_COMPLETE.YEAR, 
       PAYCARD_ITEMS.UNITS, PAYCARD_ITEMS.RATE, 
       PAYCARD_ITEMS.AMOUNT, 
       PAYROLL_CATEGORIES.NUMBER, 
       PAYROLL_CATEGORIES.KIND, 
       PAYROLL_CATEGORIES.ABBREVIATION, 
       PAYROLL_CATEGORIES.DESCRIPTION, 
       PAYROLL_CATEGORIES.SHOW_PAYSTUB, 
       (SELECT SUM(A.AMOUNT) FROM PAYCARD_ITEMS A,  PAYCARDS B WHERE B.ID = 
A.ID_PAY_CARD AND B.ID_EMPLOYEE = PAYCARDS_COMPLETE.ID_EMPLOYEE AND B.YEAR =
 PAYCARDS_COMPLETE.YEAR AND PAYCARD_ITEMS.ID_PAYROLL_CATEGORY =
 A.ID_PAYROLL_CATEGORY AND B.ID <= PAYCARDS_COMPLETE.ID_PAYCARD) SELECT_SUM_A_AMOUNT_FROM
FROM PAYCARDS_COMPLETE PAYCARDS_COMPLETE
      FULL OUTER JOIN PAYCARD_ITEMS PAYCARD_ITEMS ON 
     (PAYCARD_ITEMS.ID_PAY_CARD = PAYCARDS_COMPLETE.ID_PAYCARD)
      FULL OUTER JOIN PAYROLL_CATEGORIES PAYROLL_CATEGORIES ON 
     (PAYROLL_CATEGORIES.ID = PAYCARD_ITEMS.ID_PAYROLL_CATEGORY)
ORDER BY PAYCARDS_COMPLETE.ID_PAYCARD, 
         PAYROLL_CATEGORIES.NUMBER

It returns exactly the dollar amounts I want. However, even though I am using full outer joins, it is only returning only the PAYROLL_CATEGORY lines which exist in PAYCARD_ITEMS on that particular paycard. Sorry if it's a confusing looking query.

Any ideas how to fix this query so that all of the possible payroll categories in the PAYROLL_CATEGORIES table are included in the results?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 10:48:04

Please edit that post, adding some carriage returns in to that really long line. Long lines like that screw up the HTML formatting.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SBLatta
Starting Member

33 Posts

Posted - 2010-05-10 : 10:48:42

Sorry, was doing that as you posted.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 10:58:33

This is just a guess. Replace this:

(SELECT SUM(A.AMOUNT) FROM PAYCARD_ITEMS A,  PAYCARDS B WHERE B.ID = 
A.ID_PAY_CARD AND B.ID_EMPLOYEE = PAYCARDS_COMPLETE.ID_EMPLOYEE AND B.YEAR =
 PAYCARDS_COMPLETE.YEAR AND PAYCARD_ITEMS.ID_PAYROLL_CATEGORY =
 A.ID_PAYROLL_CATEGORY AND B.ID <= PAYCARDS_COMPLETE.ID_PAYCARD)

with this:

(SELECT ISNULL(SUM(A.AMOUNT) , 0)
FROM PAYCARD_ITEMS A
FULL OUTER JOIN PAYCARDS B 
	ON B.ID = A.ID_PAY_CARD 
	AND B.ID_EMPLOYEE = PAYCARDS_COMPLETE.ID_EMPLOYEE 
	AND B.YEAR = PAYCARDS_COMPLETE.YEAR 
	AND PAYCARD_ITEMS.ID_PAYROLL_CATEGORY = A.ID_PAYROLL_CATEGORY 
	AND B.ID <= PAYCARDS_COMPLETE.ID_PAYCARD)

The first one is similar to an inner join, the second is obviously an outer join, which may be what you require.

If that doesn't work, then post some sample data, and the result you expect. It's always easier to debug queries with sample data. Make sure the sample data highlights the situation you're trying to resolve.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SBLatta
Starting Member

33 Posts

Posted - 2010-05-10 : 11:19:59

Sample results...

What I'm getting:

ID_PAYCARD ID_EMPLOYEE PAY_DATE RUN_NUMBER YEAR UNITS RATE AMOUNT NUMBER KIND ABBREVIATION DESCRIPTION SHOW_PAYSTUB Column1
5246 24 1/15/2010 1 2010 0 0 5000 101 E Salary Salary Y 5000
5598 24 1/29/2010 2 2010 0 0 5000 101 E Salary Salary Y 10000
5651 24 1/29/2010 2 2010 0 0 30852.58 112 E Commission Commission Y 30852.58
5818 24 2/12/2010 3 2010 0 0 5000 101 E Salary Salary Y 15000
6170 24 2/26/2010 4 2010 0 0 5000 101 E Salary Salary Y 20000
6318 24 2/26/2010 4 2010 0 0 19140.01 112 E Commission Commission Y 49992.59
6573 24 3/15/2010 5 2010 0 0 5000 101 E Salary Salary Y 25000
6822 24 3/31/2010 6 2010 0 0 27056.04 112 E Commission Commission Y 77048.63
6825 24 3/31/2010 6 2010 0 0 5000 101 E Salary Salary Y 30000
7074 24 4/15/2010 7 2010 0 0 5000 101 E Salary Salary Y 35000
7323 24 4/30/2010 8 2010 0 0 5000 101 E Salary Salary Y 40000
7371 24 4/30/2010 8 2010 0 0 32481.95 112 E Commission Commission Y 109530.58

What I want:

ID_PAYCARD ID_EMPLOYEE PAY_DATE RUN_NUMBER YEAR UNITS RATE AMOUNT NUMBER KIND ABBREVIATION DESCRIPTION SHOW_PAYSTUB Column1
5246 24 1/15/2010 1 2010 0 0 5000 101 E Salary Salary Y 5000
5598 24 1/29/2010 2 2010 0 0 5000 101 E Salary Salary Y 10000
5651 24 1/29/2010 2 2010 0 0 30852.58 112 E Commission Commission Y 30852.58
5818 24 2/12/2010 3 2010 0 0 5000 101 E Salary Salary Y 15000
5818 24 2/12/2010 3 2010 0 0 0 112 E Commission Commission Y 30852.58
6170 24 2/26/2010 4 2010 0 0 5000 101 E Salary Salary Y 20000
6318 24 2/26/2010 4 2010 0 0 19140.01 112 E Commission Commission Y 49992.59
6573 24 3/15/2010 5 2010 0 0 5000 101 E Salary Salary Y 25000
6573 24 2/26/2010 4 2010 0 0 0 112 E Commission Commission Y 49992.59
6822 24 3/31/2010 6 2010 0 0 27056.04 112 E Commission Commission Y 77048.63
6825 24 3/31/2010 6 2010 0 0 5000 101 E Salary Salary Y 30000
6825 24 3/31/2010 6 2010 0 0 0 112 E Commission Commission Y 77048.63
7074 24 4/15/2010 7 2010 0 0 5000 101 E Salary Salary Y 35000
7074 24 4/15/2010 7 2010 0 0 0 112 E Commission Commission Y 77048.63
7323 24 4/30/2010 8 2010 0 0 5000 101 E Salary Salary Y 40000
7371 24 4/30/2010 8 2010 0 0 32481.95 112 E Commission Commission Y 109530.58

The problem seems to be that with the query I have, I seem to be needing to add lines to the PAYCARD_ITEMS results for items which aren't there. I think I need to find a way to return a list of all PAYROLL_CATEGORIES items and then use subqueries to return the unit, rate, amount and YTD sum(Amount) for the paycard. I don't know how to do that.

SBLatta
Starting Member

33 Posts

Posted - 2010-05-10 : 11:25:02

quote:
Originally posted by DBA in the making

(SELECT ISNULL(SUM(A.AMOUNT) , 0)
FROM PAYCARD_ITEMS A
FULL OUTER JOIN PAYCARDS B
ON B.ID = A.ID_PAY_CARD
AND B.ID_EMPLOYEE = PAYCARDS_COMPLETE.ID_EMPLOYEE
AND B.YEAR = PAYCARDS_COMPLETE.YEAR
AND PAYCARD_ITEMS.ID_PAYROLL_CATEGORY = A.ID_PAYROLL_CATEGORY
AND B.ID <= PAYCARDS_COMPLETE.ID_PAYCARD)

The first one is similar to an inner join, the second is obviously an outer join, which may be what you require.

DBA, I tried your code and it returned the same results mine did. See my comment at the end of the previous post about where I think the the problem is.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 12:32:10

I see what you're trying to do now. FULL OUTER JOIN doesn't work that way. It won't force the Commission record for ID_PAYCARD = 5818. It just makes sure that the Commission record is used, even if it doesn't do anything.

What you need to do is union the result you have with another query that specifically looks up those missing record. Without knowing the intimate details of your situation, it's a little difficult for me to give you an exact query. However, I can give you some steps that may help.

Write a query that returns the ID_PAYCARD for the records that are missing. Once you have this list, then join it to PAYROLL_CATEGORIES, using "ON PAYROLL_CATEGORIES.ID = [Category ID for commission]" to get the remainder of the fields from the original query. It really depends on where the join in the existing query is broken. ie, which table the records are missing from.

Once you have a query that returns the rows which are missing from the original query, you can union them together.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SBLatta
Starting Member

33 Posts

Posted - 2010-05-11 : 11:04:11

Let's see if this helps get people thinking, because I am truly stuck:

CREATE TABLE PAYROLL_CATEGORIES
([ID] integer,
[NUMBER] varchar(3),
[ABBREVIATION] varchar(10),
[DESCRIPTION] varchar(25),
[KIND] varchar(1),
[SHOW_PAYSTUB] varchar(1));

CREATE TABLE PAYCARDS_COMPLETE
([ID] integer,
[ID_EMPLOYEE] integer,
[PAY_DATE] datetime,
[YEAR] integer);

CREATE TABLE PAYCARD_ITEMS
([ID] integer,
[ID_PAYCARD] integer,
[ID_PAYROLL_CATEGORY] integer,
[UNITS] double,
[RATE] double,
[AMOUNT] currency);

INSERT INTO PAYROLL_CATEGORIES VALUES (1,'100','Salary','Salary','E','Y'), (2,'101','Commission','Commission','E','Y');

INSERT INTO PAYCARDS_COMPLETE VALUES (6700,1,2010-01-15,2010), (6845,1,2010-01-31,2010), (6912,1,2010-01-31,2010), (7013,1,2010-02-15,2010), (7209,1,2010-02-28,2010), (7305,1,2010-02-28,2010);

INSERT INTO PAYCARD_ITEMS VALUES (1,6700,1,0,0,5000), (2,6845,1,0,0,5000), (3,6912,2,0,0,2342.62), (4,7013,1,0,0,5000), (5,7209,1,0,0,5000), (6,7305,2,0,0,2670.91);

The tables are joined by PAYROLL_CATEGORIES.ID = PAYCARD_ITEMS.ID_PAYROLL_CATEGORY and PAYCARDS_COMPLETE.ID = PAYCARD ITEMS.ID_PAYCARD.

So now ID_EMPLOYEE 1 has 6 paycards in PAYCARDS_COMPLETE. Four of those contain Salary pay only. Two of them contain Commission pay only. I need a query which will return for every paycard in PAYCARDS_COMPLETE, a line for every item in PAYROLL_CATEGORIES containing the UNITS, RATE and AMOUNT (if any) as well as the year to date total of AMOUNT for that ID_EMPLOYEE and where the YTD total of AMOUNT <> 0. My query in the first post is my best effort at it, but it is excluding payroll categories which are not in PAYCARD_ITEMS for a given paycard.

For paycard 6, I would expect the Salary Amount to be 0 and YTD total to be 20000 and the Commission Amount to be 2670.91 and the YTD total to be 5013.53.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources