Sherlock Holmes I Am Not- Finding Missing Deductions and Benefits

This week I am spending a couple of days back in the old neighborhood (Denver, Colorado) training a client on reporting tools (Report Writer, SmartList Builder, and SQL Reporting Services).  After spending this morning discussing table structure and selecting a reporting tool, we dove in to working on reports this afternoon.  In our brainstorming session for reporting ideas/issues, I was asked for a way to "check" if employees had been set up for the occupational tax here in Denver.  We do this by assigning to a benefit and deduction for the "head tax", rather than using the local tax feature in GP as there are some unique aspects to how Denver calculates the tax.

It is easy enough with SmartList or even Report Writer to test for those that do have the benefit or deduction.  But it becomes a little more difficult to test for something that is not there.  Especially when the employees would have other deductions and benefits, so we really only want to know those that are not set up for these specific codes.  So after a lovely visit to the new IKEA store here in Colorado (oh, my, yes I love me IKEA), and a decent dinner, I came back to the hotel to chew on how to best test for something not existing.  And in that, I found my answer...the NOT EXISTS function in SQL.  Ah, yes. 

Here is what I came up with to test for both scenarios.  The first part tests for the OCCPAT deduction, and displays the employee ID, name, and a message if it does not exist for a record.  And then the second part tests for the corresponding OOCCPUP benefit and returns a similar set of data.  One lovely list.  To be included in my lovely SQL report.

SELECT a.employid, a.frstname, a.lastname, 'NO OCCPAT Deduction' from UPR00100 as a where not exists (select * from UPR00500 as b join UPR00100 as c on c.employID=b.employID where c.employid=a.employid and b.deducton='OCCPAT') and a.inactive=0
UNION
SELECT a.employid, a.frstname, a.lastname, 'NO OOCCUP Benefit' from UPR00100 as a where not exists (select * from UPR00600 as b join UPR00100 as c on c.employID=b.employID where c.employid=a.employid and b.benefit='OOCCUP') and a.inactive=0

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.