Dynamics GP‎ > ‎

SQL query to summarize pay stubs

Dynamics GP check history is in the UPR30100 table in the database.

This sql statement summarizes the key information in the check history table, essentially re-creating someone's W-2 information.

select * from
(
select employid, EMPLNAME, sum(grwgprn) as gross, sum(fdwgpyrn) as federalwages,sum(ficamwgp) as medicarewages, sum(ficasswp) as sswages, 
sum(fdwdgprn) as federal, sum(ficamwpr) as medicare, sum(fcaswpr) as fica, sum(ntwpyrn) as net ,
sum(grwgprn)-sum(fdwdgprn)- sum(ficamwpr) - sum(fcaswpr)-sum(ttlddtns) -
(sum(totltaxs)-sum(fdwdgprn)-sum(ficamwpr) -sum(fcaswpr))
as netshouldbe, sum(totltaxs) as totaltax,
sum(totltaxs)-sum(fdwdgprn)-sum(ficamwpr) -sum(fcaswpr) as estimatedstatetax, sum(ttlddtns) as totaldeductions
from upr30100 
where year(chekdate)=2017 and VOIDED=0
group by employid, EMPLNAME
) as a order by sswages desc



Comments