Update an employee's max deduction after turning 50

Disclaimer:

Some of this is accounting and state/federal law, neither of which I'm an expert in. I'm simply documenting a piece of code that I wrote for a consultant request. 

The request was this:

The Dynamics GP Payroll Deduction setup is in UPR40900. That table holds a max allowable deduction per year (specifically, we're concerned with 401K deductions)

When an employee turns 50, we need to increase that max deduction by $6000 in the UPR00500 Employee Deduction Setup table. 

Write a script to do that on their 50th birthday. 

So, here it is -->

 

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

-- look at all employees that are 50 - 55
select e.employid, e.BRTHDATE,ded.DEDUCTON,
        ds.dedyrmax as SetupDeductionYearMax,
        ded.dedyrmax as EmpDeductionYearMax
    from upr00100 e
        join upr00500 ded on ded.EMPLOYID = e.EMPLOYID and ded.DEDUCTON = '401k'
        join UPR40900 ds on ds.DEDUCTON = ded.DEDUCTON
     
    where
        --employees between 50 and 55
        e.BRTHDATE between dateadd(month, -((55 * 12)), getdate()) and dateadd(month, -((50 * 12)), getdate())
    order by brthdate
 
--update the max deduction for this employee set
update upr00500 set DEDYRMAX = ds.DEDYRMAX + 6000
    from upr00100 e
        join upr00500 ded on ded.EMPLOYID = e.EMPLOYID and ded.DEDUCTON = '401k'
        join UPR40900 ds on ds.DEDUCTON = ded.DEDUCTON
     
    where
        --employees between 50 and 55
        e.BRTHDATE between dateadd(month, -((55 * 12)), getdate()) and dateadd(month, -((50 * 12)), getdate())

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences