I'm doing some side contract work (SQL Server Integration Services development) for a client that's in the financial services industry. I'm automating a process that they currently run manually once per quarter. The process involves hitting their Easy Projects database, collecting info. on employees associated with various employers, filtering the employees by several pieces of criteria, and emailing each employer's representative with a list of employees that meet our filtering criteria.
The client isn't overly familiar with the table structures behind the scenes of their Easy Projects database (nor am I), but we've gone through a few sessions and have identified nearly enough tables and the appropriate relationships between these couple of tables to be able to assemble most of the required info.
One piece we can't seem to pin-down is the association of employers with their respective employees as far as trying to query the appropriate tables to obtain this info. The client can navigate to one of a few screens in the Easy Projects Web interface and we obviously can see that when viewing employers (or "Plans" as we refer to them), we see line after line of employees associated with each employer or plan.
I just can't seem to figure out the behind the scenes location or tie-in that allows me to show the association of employers and employees when I'm writing various statements in SQL Server, although I know the info. and associations are indeed there.
I want to say that some of what I'm tracking in terms of employers / employees is present as custom fields created by my client, so I'm guessing that's further complicating being able to easily figure out how to tie-in this employee/employer relationship in the queries I'm trying to write.
Any recommendations are appreciated. We may just have to initiate a call or message directly to Easy Projects support to try to assist with this otherwise.