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