I've having difficulty joining two tables and getting the results I want. What I'm trying to accomplish is an Health Savings Account (HSA) upload file to go to our Insurance vendor. Here's a summary
Table1: PS_AL_CHK_DED
Description: This table stores all deductions in an employee's paycheck. I'm specifically looking for a field in that table called AL_DEDCD where it equals "H". This is the deduction from the employee's paycheck.
EXAMPLE:
EMPLID...........CHECK_DT...........AL_DEDCD.............AL_AMOUNT
123456............6/30/2015..............H.............................100.00
123456............6/30/2015..............R..............................75.00
987654............6/30/2015..............H..............................50.00
987654............6/30/2015..............A..............................85.00
456123............6/30/2015..............H..............................200.00
456123............6/30/2015..............L...............................25.00
Table2: PS_AL_CHK_MEMO
Description: This table stores the employer contribution based on their deduction. For example, we match 50% of their contribution. It is possible for the employee to have a deduction (stored in the ps_al_chk_ded table) but it is not possible for them to have an employer contribution (stored in ps_al_chk_memo) without a deduction (stored in the ps_al_chk_ded).
EXAMPLE:
EMPLID..........................CHECK_DT........................MEMO_CD...................AL_AMOUNT
123456..............................6/30/2015........................H..................................50.00
123456..............................6/30/2015........................XX................................10.00
987654..............................6/30/2015........................H..................................25.00
987654..............................6/30/2015........................ZZ................................20.00
456123..............................6/30/2015........................MM. ............................40.00
456123..............................6/30/2015........................NN................................5.00
What I want in my output is a combination of these tables that looks at the employee in the ps_al_chk_ded table, takes only the records where al_dedcd = H and returns the corresponding employer contribution (memo_cd =H in al_chk_memo). So you almost have to join the two tables on all three of those fields and I can't get it to work. Here is what I would like the data to look like:
EMPLID...........CHECK_DT...........AL_DEDCD.............AL_AMOUNT(1)...............AL_AMOUNT(2)
123456............6/30/2015..............H.............................100.00.............................50.00
987654............6/30/2015..............H..............................50.00..............................25.00
456123............6/30/2015..............H..............................200.00............................0.00
This didn't seem like it was too complicated but no matter how I do it, I can never get the appropriate amount from the employer contribution. I attached a screenshot that shows my queries and result set. In the screenshot, it returned the employee deduction just fine but then returns 48.25 for the employer contribution. However, the MEMO_CD in that specific record is equal to T. In this case, it should return 0.00 for the employer contribution. If I try and add something like d.al_dedcd = c.memo_cd on the join, I barely get any results. The key is matching on those fields and then returning 0.00 when there is no record of MEMO_CD = H in the al_chk_memo table for the deduction in ps_al_chk_memo.
http://s000.tinyupload.com/?file_id=05650922404523166633
I tried to be as descriptive as possible. We are using SQL 2008 and the software system is ADP Enterprise.
Thanks,
Brian