July 17, 2009 at 3:52 pm
Hey guys, i've been working on this sql all day, and now i'm just so frustrated I can't think this thru anymore.
I have a job that will take a File and uploads its to a Payroll table, then it qualifys the clients that we are interested in and moves them to an Expense table. Well, i found a situation where we are getting duplicate entries on the Expense table, so i developed a simple query to count the records by vendor on the input file and also the records that were moved to the Expense table and joins those two counts.
Well, i would like to know if i can qualify that sql even further so that only the records where the counts do not match show up in the report. So if the column called PayrollCount does not match ExpenseCount, then display it. At this point, all the records with a count in expense shows up.
Here's my sql.
Select P.vendorName, Count(P.vendorName) as PayrollCount, E.vendorName, e.ExpenseCount
FROMdbo.PAYROLLP
INNER JOIN
(Select E.vendorName, Count(E.vendorName) as ExpenseCount
from dbo.Expense E
where E.invoiceDate = '2009-06-30 00:00:00.000'
GROUP BY E.vendorName) E
ONP.vendorName = E.vendorName
--where PayrollCount != e.ExpenseCount
GROUP BY P.vendorName, E.vendorName, e.ExpenseCount
here's what my results look like:
vendorName PayrollCountvendorNameExpenseCount
FISHER,KERRE 13 FISHER,KERRE 13
JOHNSON,JAMES D 10 JOHNSON,JAMES D 10
LEWIS,JILL 16 LEWIS,JILL 8
LITTRELL,JAMES 13LITTRELL,JAMES9
MACKIN,JAMES S 9MACKIN,JAMES S9
MAYBERRY,GEORGE 9MAYBERRY,GEORGE E9
MILLER,SHANE A 12MILLER,SHANE A4
OWENS,SHELLEY K 20OWENS,SHELLEY K18
SAPINOSO,MARIA 26SAPINOSO,MARIA L26
SMITH,EDWARD 9SMITH,EDWARD9
YOUNG,SONYA L 17YOUNG,SONYA L13
July 17, 2009 at 3:58 pm
:w00t: OOPS, sorry guys, that posted before i could format that table data better.
vendorName PayrollCountvendorNameExpenseCount
FISHER,KERRE 13 FISHER,KERRE 13
JOHNSON,JAMES D 10 JOHNSON,JAMES D 10
LEWIS,JILL 16 LEWIS,JILL 8
LITTRELL,JAMES 13 LITTRELL,JAMES 9
MACKIN,JAMES S 9 MACKIN,JAMES S 9
MAYBERRY,GEORGE E9 MAYBERRY,GEORGE E9
MILLER,SHANE A 12 MILLER,SHANE A 4
OWENS,SHELLEY K 20 OWENS,SHELLEY K 18
SAPINOSO,MARIA L26 SAPINOSO,MARIA L26
SMITH,EDWARD 9 SMITH,EDWARD 9
YOUNG,SONYA L 17 YOUNG,SONYA L 13
Can i qualify that SQL so only JILL LEWIS, JAMES LITTRELL, SHANE A. MILLER SHELLEY K OWENS, SONYA YOUNG ARE RETURNED AND NOT ALL THE ONES WHERE THE COUNTS MATCH?
July 18, 2009 at 3:35 am
Hi Lisa,
the following code might work. It's untested since I don't have anything to test against.
If you're looking for a tested solution, please provide test data as described in the link in my signature.
This will also lead to faster answers, because some of the people around here start with answering questions that contain ready to use sample data (at least I do...).
Select P.vendorName, Count(P.vendorName) as PayrollCount, E.vendorName, e.ExpenseCount
FROM dbo.PAYROLL P
INNER JOIN
(Select E.vendorName, Count(E.vendorName) as ExpenseCount
from dbo.Expense E
where E.invoiceDate = '2009-06-30 00:00:00.000'
GROUP BY E.vendorName) E
ON P.vendorName = E.vendorName
GROUP BY P.vendorName, E.vendorName, e.ExpenseCount
HAVING Count(P.vendorName) e.ExpenseCount
July 20, 2009 at 9:36 am
thank you! That worked perfectly. I was trying to use another qualifier for "not equals".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply