July 26, 2015 at 11:01 am
Jeff Moden (7/26/2015)
There are a couple of things to note. First, there are NO matches in the data where a company match actually occurred according to the match of AL_DEDCD = MEMO_CD. The rows that do have an "H" Memo_CD have no matches in the deduction table by EmplID or Check_Dt, which is contrary to what you stated is possible. That may be a problem with the original data and needs further investigation.
You also say that you're using Crystal Reports. The problem may actually lay there but I don't know enough about Crystal Reports to be able to help there at all.
The bottom line is that I'm thinking this is a data problem because the code is correct for the given data. Additional research into the data on your part is going to be the key.
Jeff...as usual you have provided an informative analysis for the OP to consider.
Far more eloquent, patient and concise than my initial reaction and potential post this morning 😀
heres hoping that Brian can review the data and provide working examples of his problem
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 26, 2015 at 11:56 am
Thanks for taking time to go through this Jeff. ADP assured me that the calculation was performing correctly but it's obviously not. I dumped both tables into excel for the 7/6 payroll test data and you are correct that there are no matches.
I was just focused on there being 70 records in ps_al_chk_ded and 44 in ps_al_chk_memo but didn't think to look to see if there was a single employee with both.
At least after ADP fixes the custom programming for the HSA calculation, you have provided me with with the solution.
As far as Crystal, it's just a way for us to distribute the reports. The working SQL query will be put into Crystal and it allows me to put parameters on it and distribute to users.
July 26, 2015 at 12:43 pm
bgolembiesky 4883 (7/26/2015)
Thanks for taking time to go through this Jeff. ADP assured me that the calculation was performing correctly but it's obviously not. I dumped both tables into excel for the 7/6 payroll test data and you are correct that there are no matches.I was just focused on there being 70 records in ps_al_chk_ded and 44 in ps_al_chk_memo but didn't think to look to see if there was a single employee with both.
At least after ADP fixes the custom programming for the HSA calculation, you have provided me with with the solution.
As far as Crystal, it's just a way for us to distribute the reports. The working SQL query will be put into Crystal and it allows me to put parameters on it and distribute to users.
You're a good man, Brian. You did exactly what I would have done. Write the code, beat the hell out of it, doubt myself a bit, ask for help, be patient with those that want to help, help them help you, find out it might be something other than the code or the DDL, verify that finding using a totally different method, inform the source that the impossible has happened, and offer them the irrefutable proof so they can fix their problem.
Other that have posted on this thread have also provided code that looks like it will work (I didn't take the time to verify one way or another) so, although I thank you for your comment, it wasn't just me. JLS took the time to fix problems in the data you posted and you posting the data was what all of us were waiting for because the problem with the code seemed impossible. Of course, the data proved that the problem wasn't in the code and you likely wrote correct code for this to begin with.
I have to admit that is also scares the willies out of me to think that such a well established company as ADP may (still to be proven one way or another) have provided incorrect data (it did fail at least one test). Once this has been corrected, we'd all love to learn what the actual problem turned out to be. Maybe the data IS correct and the presumptions (requirements) are actually incorrect. At this point, we don't really know and would love to hear from you when you find out.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2015 at 1:16 pm
J Livingston SQL (7/26/2015)
Jeff...as usual you have provided an informative analysis for the OP to consider.Far more eloquent, patient and concise than my initial reaction and potential post this morning 😀
I know you, ol' friend. And, you give me too much credit (thank you for the kind words). I, too, would have suggested that the OP try to run his own code to see what we saw and as a suggestion that he should run his own code prior to posting it in the future. You just beat me too it. 😀 And thank you for fixing his data post. It kept me from swearing on a Sunday. 😀
There wasn't so much patience on my part. You and other folks did the hard work. After looking at the posted code and the other posts, I was pretty sure (strip away all that is untrue and what remains has to be the truth) there was a data problem but had no real data to go on.
I could still be wrong there but Brian has proven that an unexpected condition lives in the data and is taking the steps to correct it with his 3rd party vendor.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2015 at 8:34 am
Jeff Moden (7/26/2015)
bgolembiesky 4883 (7/26/2015)
Thanks for taking time to go through this Jeff. ADP assured me that the calculation was performing correctly but it's obviously not. I dumped both tables into excel for the 7/6 payroll test data and you are correct that there are no matches.I was just focused on there being 70 records in ps_al_chk_ded and 44 in ps_al_chk_memo but didn't think to look to see if there was a single employee with both.
At least after ADP fixes the custom programming for the HSA calculation, you have provided me with with the solution.
As far as Crystal, it's just a way for us to distribute the reports. The working SQL query will be put into Crystal and it allows me to put parameters on it and distribute to users.
You're a good man, Brian. You did exactly what I would have done. Write the code, beat the hell out of it, doubt myself a bit, ask for help, be patient with those that want to help, help them help you, find out it might be something other than the code or the DDL, verify that finding using a totally different method, inform the source that the impossible has happened, and offer them the irrefutable proof so they can fix their problem.
Other that have posted on this thread have also provided code that looks like it will work (I didn't take the time to verify one way or another) so, although I thank you for your comment, it wasn't just me. JLS took the time to fix problems in the data you posted and you posting the data was what all of us were waiting for because the problem with the code seemed impossible. Of course, the data proved that the problem wasn't in the code and you likely wrote correct code for this to begin with.
I have to admit that is also scares the willies out of me to think that such a well established company as ADP may (still to be proven one way or another) have provided incorrect data (it did fail at least one test). Once this has been corrected, we'd all love to learn what the actual problem turned out to be. Maybe the data IS correct and the presumptions (requirements) are actually incorrect. At this point, we don't really know and would love to hear from you when you find out.
I just realized that I never thanked JLS for the help and correcting the errors. That was my mistake. We paid ADP to do custom programming for us a while back and it's been a frustrating couple of months getting it to this point. In the future, I know the proper way to get help here now though.
In any event, I spoke with ADP and there was misinformation given. The deduction code I need to pull out of ps_al_chk_ded is HSP7. So the end result is that I need to join ed.al_dedcd and ec.memo_cd but the actual values are HSP7 in one table and H in the other. I tried adding a select statement inside the join for ed.al_dedcd = ec.memo_cd but I'm not getting any results. This time, I know it's an error with my code since I can isolate employees and see they have a deduction (HSP7) and also a match (H) for the same check dates. I'm not sure how to create the code to join those fields when the data we are matching on is different.
Any help would be appreciated.
July 27, 2015 at 9:19 am
How about just this?
SELECT ED.EMPLID, ED.CHECK_DT, ED.AL_DEDCD, ED.AL_AMOUNT AS HSA_DEDUCTION, EC.AL_AMOUNT AS EMPLYR_HSA_CONTRIB
FROM PS_AL_CHK_DED AS ED
LEFT OUTER JOIN PS_AL_CHK_MEMO AS EC
ON ED.EMPLID = EC.EMPLID
AND ED.CHECK_DT = EC.CHECK_DT
AND EC.MEMO_CD = 'HSP7'
WHERE ED.AL_DEDCD = 'H'
Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 9:38 am
sgmunson (7/27/2015)
How about just this?
SELECT ED.EMPLID, ED.CHECK_DT, ED.AL_DEDCD, ED.AL_AMOUNT AS HSA_DEDUCTION, EC.AL_AMOUNT AS EMPLYR_HSA_CONTRIB
FROM PS_AL_CHK_DED AS ED
LEFT OUTER JOIN PS_AL_CHK_MEMO AS EC
ON ED.EMPLID = EC.EMPLID
AND ED.CHECK_DT = EC.CHECK_DT
AND EC.MEMO_CD = 'HSP7'
WHERE ED.AL_DEDCD = 'H'
Let me know...
Thanks for your help, SG. I tried something simple like that and it didn't work. Here's what I tried and got no results:
SELECT ED.EMPLID, ED.CHECK_DT, ED.AL_DEDCD, ED.AL_AMOUNT AS HSA_DEDUCTION, EC.AL_AMOUNT AS EMPLYR_HSA_CONTRIB
FROM PS_AL_CHK_DED AS ED
LEFT OUTER JOIN PS_AL_CHK_MEMO AS EC
ON ED.EMPLID = EC.EMPLID
AND ED.CHECK_DT = EC.CHECK_DT
AND ED.AL_DEDCD = EC.MEMO_CD
WHERE (ED.AL_DEDCD = 'HSP7' or EC.MEMO_CD = 'H') and ED.CHECK_DT = '7/6/2015'
When I tried your suggestion, I noticed that you had ed.al_dedcd = H and ec.memo_cd = HSP7. But that's actually backwards. The value of HSP7 in in ed.al_dedcd. When I swaped the field names from your query, it pulls in the correct EMPLYR_HSA_CONTRIB but now HSA_DEDUCTION is wrong. For some reason, the query you posted is bringing in ED.AL_DEDCD = 9. See below for results:
EMPLID CHECK_DT AL_DEDCD HSA_DEDUCTION EMPLYR_HSA_CONTRIB
----------- ----------------------- -------- --------------------------------------- ---------------------------------------
053781223 2015-07-20 00:00:00.000 9 10.00 5.00
097746316 2015-07-20 00:00:00.000 9 10.00 5.00
164683722 2015-07-20 00:00:00.000 9 15.00 7.50
166663461 2015-07-20 00:00:00.000 9 20.00 10.00
169446539 2015-07-20 00:00:00.000 9 21.15 10.58
169628400 2015-07-20 00:00:00.000 9 7.50 3.75
169723771 2015-07-20 00:00:00.000 9 19.23 17.25
176708942 2015-07-20 00:00:00.000 9 15.00 7.50
177686828 2015-07-20 00:00:00.000 9 23.25 11.63
178640526 2015-07-20 00:00:00.000 9 10.00 5.00
182606284 2015-07-20 00:00:00.000 9 20.00 10.00
182705355 2015-07-20 00:00:00.000 9 9.62 4.81
183529454 2015-07-20 00:00:00.000 9 40.00 20.00
186609170 2015-07-20 00:00:00.000 9 20.00 10.00
191685301 2015-07-20 00:00:00.000 9 20.00 10.00
191707091 2015-07-20 00:00:00.000 9 9.62 4.81
191708503 2015-07-20 00:00:00.000 9 30.00 15.00
192541401 2015-07-20 00:00:00.000 9 20.00 10.00
193582141 2015-07-20 00:00:00.000 9 19.23 9.62
200583853 2015-07-20 00:00:00.000 9 25.00 12.50
206508413 2015-07-20 00:00:00.000 9 20.00 10.00
214253311 2015-07-20 00:00:00.000 9 20.00 10.00
215025357 2015-07-20 00:00:00.000 9 20.00 10.00
219315616 2015-07-20 00:00:00.000 9 15.00 7.50
221744208 2015-07-20 00:00:00.000 9 7.00 52.50
222602984 2015-07-20 00:00:00.000 9 21.00 0.50
225615006 2015-07-20 00:00:00.000 9 20.00 10.00
232375215 2015-07-20 00:00:00.000 9 40.00 20.00
234259753 2015-07-20 00:00:00.000 9 25.00 12.50
234355161 2015-07-20 00:00:00.000 9 30.00 15.00
235115195 2015-07-20 00:00:00.000 9 8.00 4.00
235962096 2015-07-20 00:00:00.000 9 10.00 5.00
236213440 2015-07-20 00:00:00.000 9 38.47 19.24
236319961 2015-07-20 00:00:00.000 9 10.00 5.00
273945732 2015-07-20 00:00:00.000 9 20.00 10.00
294585540 2015-07-20 00:00:00.000 9 11.54 5.77
359568789 2015-07-20 00:00:00.000 9 19.24 9.62
419333545 2015-07-20 00:00:00.000 9 9.62 4.81
583153622 2015-07-20 00:00:00.000 9 19.00 9.50
(39 row(s) affected)
July 27, 2015 at 9:41 am
or this....similar to Steve but altered the filters accordingly
SELECT ED.EMPLID, ED.CHECK_DT, ED.AL_DEDCD, ED.AL_AMOUNT AS HSA_DEDUCTION, EM.AL_AMOUNT AS EMPLYR_HSA_CONTRIB
FROM PS_AL_CHK_DED AS ED
LEFT OUTER JOIN PS_AL_CHK_MEMO AS EM
ON ED.EMPLID = EM.EMPLID
AND ED.CHECK_DT = EM.CHECK_DT
AND EM.MEMO_CD = 'H'
WHERE ED.DEDCD = 'HSP7'
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 27, 2015 at 9:47 am
J Livingston SQL (7/27/2015)
or this....similar to Steve but altered the filters accordingly
SELECT ED.EMPLID, ED.CHECK_DT, ED.AL_DEDCD, ED.AL_AMOUNT AS HSA_DEDUCTION, EM.AL_AMOUNT AS EMPLYR_HSA_CONTRIB
FROM PS_AL_CHK_DED AS ED
LEFT OUTER JOIN PS_AL_CHK_MEMO AS EM
ON ED.EMPLID = EM.EMPLID
AND ED.CHECK_DT = EM.CHECK_DT
AND EM.MEMO_CD = 'H'
WHERE ED.DEDCD = 'HSP7'
Thanks, JLS. Your code is exactly what I modified Steve's to be. For some reason, it's returning only AL_DED codes of 9 and giving the wrong value. I can't figure out why it's picking up that deduction code. I posted the results from the query into my post directly above yours.
July 27, 2015 at 10:00 am
bgolembiesky 4883 (7/27/2015)
Thanks for your help, SG. I tried something simple like that and it didn't work. Here's what I tried and got no results:
SELECT ED.EMPLID, ED.CHECK_DT, ED.AL_DEDCD, ED.AL_AMOUNT AS HSA_DEDUCTION, EC.AL_AMOUNT AS EMPLYR_HSA_CONTRIB
FROM PS_AL_CHK_DED AS ED
LEFT OUTER JOIN PS_AL_CHK_MEMO AS EC
ON ED.EMPLID = EC.EMPLID
AND ED.CHECK_DT = EC.CHECK_DT
AND ED.AL_DEDCD = EC.MEMO_CD
WHERE (ED.AL_DEDCD = 'HSP7' or EC.MEMO_CD = 'H') and ED.CHECK_DT = '7/6/2015'
When I tried your suggestion, I noticed that you had ed.al_dedcd = H and ec.memo_cd = HSP7. But that's actually backwards. The value of HSP7 in in ed.al_dedcd. When I swaped the field names from your query, it pulls in the correct EMPLYR_HSA_CONTRIB but now HSA_DEDUCTION is wrong. For some reason, the query you posted is bringing in ED.AL_DEDCD = 9. See below for results:
Try this instead:
SELECT ED.EMPLID, ED.CHECK_DT, ED.AL_DEDCD, ED.AL_AMOUNT AS HSA_DEDUCTION, EC.AL_AMOUNT AS EMPLYR_HSA_CONTRIB
FROM PS_AL_CHK_DED AS ED
LEFT OUTER JOIN PS_AL_CHK_MEMO AS EC
ON ED.EMPLID = EC.EMPLID
AND ED.CHECK_DT = EC.CHECK_DT
AND EC.MEMO_CD = 'H'
WHERE ED.AL_DEDCD = 'HSP7'
AND ED.CHECK_DT = '2015-07-06'
If you keep setting the AL_DEDCD = MEMO_CD in the JOIN, there will be trouble... If what I just submitted has already been specifically attempted, just say so...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 10:31 am
JLS and Steve, thank you both very much for your help. I can finally leave the both of you alone now.
When ADP gave me the deduction code value of HSP7, I was assuming that was the al_dedcd (which is what we go off of). However, HSP7 is the dedcd and 9 is the al_dedcd corresponding value in that table.
I really appreciate the help from both of you.
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply