After creating our year end wage file, we have a few employee's who's state and federal wages are not the same on their W2. When I look at the Employee State Tax Summary and compare that to the Employee Summary, the two values total to be the same. However, the W2 is showing a lower State Taxable Wage. Why is this?
Thanks,
George
*This post is locked for comments
Hi,
This was just posted to this conversation earlier. Let me know if you have any questions.
Thanks,
Adam
Hi,
Thanks for posting the script. As Joni stated this is meant to be a starting point and does not necessarily work in every situation. Let me know if you have any follow-up questions.
Thanks,
Adam
Fantastic, thank you so much!!!!
This is the SQL script that AdamH shared with me. I haven't tried it so I can't verify its accuracy. I had no experience with SQL at that time so I turned to our partner for assistance.
/*
Disclaimer: This script is for comparing payroll details to the year end wage summary tables. Use at your own risk.
This query will pull together the details from the payroll history for a given year and compare with
the summary tables from the payroll year end wage file.
There are a couple items that need to be manipulated:
1) Verify that the @MedicareTaxRate and @SSTaxRate values are correct
2) Verify that the @PayrollYear variable is being assinged the correct value.
3) Find and change the list of any 401K deduction codes that are specific to your company
You will need to add a UNION for
4) The @ReturnDetailOnly option defaults to 0 (No) which will return the summary table.
If you want to return all the details at the check date level set this option to 1.
Assumptions/Notes:
1) Each employee only has one state payroll tax state.
2) Employees that dod not have any wages/tips/compensation will be excluded
3) Voids are excluded
*/
--Declare and populate local variables
DECLARE
@MedicareTaxRate NUMERIC(18,5),
@SSTaxRate NUMERIC(18,5),
@PayrollYear INT,
@ReturnDetailOnly BIT
SELECT
@MedicareTaxRate = '1.45',
@SSTaxRate = '4.2',
@PayrollYear = '2012',
@ReturnDetailOnly = '0'
--Declare and populate table for 401K deduction codes
DECLARE @401K TABLE (Code VARCHAR(7))
INSERT @401K (Code)
SELECT DEDUCTON FROM UPR40900 WHERE DEDUCTON IN('My','401K','List','Here')
IF @ReturnDetailOnly = 0
BEGIN
SELECT
x.EMPLCLAS AS EmployeeClass,
z.EmployID,
RTRIM(x.LASTNAME) + ', ' + RTRIM(x.FRSTNAME) + ' ' + RTRIM(x.MIDLNAME) AS EmployName,
x.BRTHDATE AS BirthDate,
x.STRTDATE AS OriginalHireDate,
x.BENADJDATE AS AdjustedHireDate,
x.DEMPINAC AS DateInactivated,
y.CITY,
y.STATE,
SUM(z.GrossWages) AS GrossWages,
z.W2WagTipComp,
SUM(z.FedGrossWages) AS FedGrossWages,
SUM(z.FedWH) AS FedWH,
z.W2FedWH,
ISNULL(SUM(z.[401KDed]),0) AS [401KDed],
SUM(z.FICAMedGrossWages) AS FicaMedGrossWages,
z.W2FICAMedGrossWages,
SUM(z.FICAMedWH) AS FicaMedWH,
z.W2FICAMedWH,
SUM(z.FICASSGrossWages) AS FicaSSGrossWages,
z.W2FICASSGrossWages,
SUM(z.FICASSWH) as FicaSSWH,
z.W2FICASSWH,
z.W2StateWages,
z.W2StateWH,
CASE WHEN CONVERT(NUMERIC(18,2),(SUM(z.FICAMedWH) / CASE WHEN SUM(z.FICAMedGrossWages) = 0 THEN NULL ELSE SUM(z.FICAMedGrossWages) END) * 100) = @MedicareTaxRate THEN 'Yes' ELSE 'No' END AS DoesFicaWHBalance,
CASE WHEN CONVERT(NUMERIC(18,2),(SUM(z.FICASSWH) / CASE WHEN SUM(z.FICASSGrossWages) = 0 THEN NULL ELSE SUM(z.FICASSGrossWages) END) * 100) = @SSTaxRate THEN 'Yes' ELSE 'No' END AS DoesFicaSSBalance,
CASE WHEN SUM(z.FICAMedGrossWages) = z.W2FICAMedGrossWages THEN 'Yes' ELSE 'No' END AS DoMedWagesEqual,
CASE WHEN SUM(z.FICASSGrossWages) = z.W2FICASSGrossWages THEN 'Yes' ELSE 'No' END AS DoSSWagesEqual,
CASE WHEN SUM(z.FICAMedWH) = z.W2FICAMedWH THEN 'Yes' ELSE 'No' END AS DoMedWHWagesEqual,
CASE WHEN SUM(z.FICASSWH) = z.W2FICASSWH THEN 'Yes' ELSE 'No' END AS DoSSWHWagesEqual
FROM (
SELECT
a.AUCTRLCD AS AuditTrail,
a.CHEKDATE AS CheckDate,
a.employid AS EmployID,
a.GRWGPRN AS GrossWages,
a.FDWDGPRN AS FedWH,
a.FICAMWPR AS FICAMedWH,
a.FCASWPR as FICASSWH,
a.FDWGPYRN AS FedGrossWages,
a.FICASSWP AS FICASSGrossWages,
a.FICAMWGP AS FICAMedGrossWages,
(SELECT SUM(UPRTRXAM) FROM UPR30300 z WHERE z.PYRLRTYP = '2' AND z.PAYROLCD IN(SELECT Code FROM @401K) AND z.EMPLOYID = a.EMPLOYID and z.AUCTRLCD = a.AUCTRLCD) AS [401KDed],
bb.WGTPCOMP AS W2WagTipComp,
bb.FEDITXWH AS W2FedWH,
bb.SSECWAGS AS W2FICASSGrossWages,
bb.SSTXWHLD AS W2FICASSWH,
bb.MCRWGTPS AS W2FICAMedGrossWages,
bb.MDCRTXWH AS W2FICAMedWH,
cc.STATEWGS AS W2StateWages,
cc.STATINTX AS W2StateWH
FROM
UPR30100 a
LEFT OUTER JOIN UPR10101 bb on bb.EMPLOYID = a.EMPLOYID AND bb.RPTNGYR = a.Year1
LEFT OUTER JOIN (SELECT EMPLOYID, SUM(STATEWGS) AS STATEWGS, SUM(STATINTX) AS STATINTX FROM UPR10105 WHERE RPTNGYR = @PayrollYear GROUP BY EMPLOYID) cc on cc.EMPLOYID = a.EMPLOYID
WHERE
a.VOIDED = '0'
AND bb.rptngyr = @PayrollYear
AND bb.wgtpcomp <> 0
) z
INNER JOIN UPR00100 x on x.EMPLOYID = z.EmployID
LEFT OUTER JOIN UPR00102 y on y.ADRSCODE = x.ADRSCODE and y.EMPLOYID = x.EMPLOYID
GROUP BY
z.W2WagTipComp,
z.W2FedWH,
z.W2FICASSGrossWages,
z.W2FICASSWH,
z.W2FICAMedGrossWages,
z.W2FICAMedWH,
z.W2StateWages,
z.W2StateWH,
z.EmployID,
x.LASTNAME,
x.FRSTNAME,
x.MIDLNAME,
x.EMPLCLAS,
y.CITY,
y.STATE,
x.BRTHDATE,
x.STRTDATE,
x.BENADJDATE,
x.DEMPINAC
END
IF @ReturnDetailOnly = 1
BEGIN
SELECT
a.AUCTRLCD AS AuditTrail,
a.CHEKDATE AS CheckDate,
a.employid AS EmployID,
RTRIM(x.LASTNAME) + ', ' + RTRIM(x.FRSTNAME) + ' ' + RTRIM(x.MIDLNAME) AS EmployName,
a.GRWGPRN AS GrossWages,
a.FDWDGPRN AS FedWH,
a.FICAMWPR AS FICAMedWH,
a.FCASWPR as FICASSWH,
a.FDWGPYRN AS FedGrossWages,
a.FICASSWP AS FICASSGrossWages,
a.FICAMWGP AS FICAMedGrossWages,
(SELECT SUM(UPRTRXAM) FROM UPR30300 z WHERE z.PYRLRTYP = '2' AND z.PAYROLCD IN(SELECT Code FROM @401K) AND z.EMPLOYID = a.EMPLOYID and z.AUCTRLCD = a.AUCTRLCD) AS [401KDed],
bb.WGTPCOMP AS W2WagTipComp,
bb.FEDITXWH AS W2FedWH,
bb.SSECWAGS AS W2FICASSGrossWages,
bb.SSTXWHLD AS W2FICASSWH,
bb.MCRWGTPS AS W2FICAMedGrossWages,
bb.MDCRTXWH AS W2FICAMedWH,
cc.STATEWGS AS W2StateWages,
cc.STATINTX AS W2StateWH
FROM
UPR30100 a
LEFT OUTER JOIN UPR10101 bb on bb.EMPLOYID = a.EMPLOYID AND bb.RPTNGYR = a.Year1
LEFT OUTER JOIN (SELECT EMPLOYID, SUM(STATEWGS) AS STATEWGS, SUM(STATINTX) AS STATINTX FROM UPR10105 WHERE RPTNGYR = @PayrollYear GROUP BY EMPLOYID) cc on cc.EMPLOYID = a.EMPLOYID
INNER JOIN UPR00100 x on x.EMPLOYID = a.EmployID
WHERE
a.VOIDED = '0'
AND bb.rptngyr = @PayrollYear
AND bb.wgtpcomp <> 0
END
Does anyone happen to have the script that they can share? We are having the same issue with State and Federal Wages not being equal.
Can I please get the SQL script too? I have the exact same scenario. Thank you.
Hi Adam. Could I please get the script too? I have the exact same problem. Thank you.
Hi Joni,
If you shoot me an email through the forum here I can send you over what I have and you can take it from there.
Thanks!
Adam
Hi ADAMH:
I have run into the problem of state taxable wages being different than federal taxable wages on our most recent payroll but haven't been able to pinpoint the problem. I have checked to make sure all 1,125 employees are currently marked for our state tax. I also went through our manual checks with a fine tooth comb, which is usually where any problems are. If something has been changed since the payroll run, it wasn't obvious to the payroll clerk. So I need another option.
Could you possibly email me the info that you were going to send to George in response to his "State and Federal Wages Not Equal" post?
Much appreciated!
Anyone else's suggestions are also welcomed!
Joni
@George -
When I have run into this in the past the most common two causes are an employee accidentally clearing the state tax on the employee tax setup card (and then setting it back) or someone setting up a paycode incorrectly and then someone subsequently altering the database for the tax flag (this is less likely).
Unfortunately, without a database backup from the same time of the payroll from that week these two things would be most likely impossible to track down.
As Douglas suggested, you could also try re-creating those situations wo see if that employees situation at that time made them exempt from the tax.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,432
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
