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) -
as netshouldbe, sum(totltaxs) as totaltax,
sum(totltaxs)-sum(fdwdgprn)-sum(ficamwpr) -sum(fcaswpr) as estimatedstatetax, sum(ttlddtns) as totaldeductions
where year(chekdate)=2017 and VOIDED=0
group by employid, EMPLNAME
) as a order by sswages desc