Summary	The analytical view is used internally to check on the data that will be included in the official “Electronic filing W2” and “W2 Form” reports at the year end
Keywords	US Payroll, W2, US report
Category	Analytical View

Description

The analytical view is used to check on the data that will be included in the report “Electronic filing W2” /wiki/spaces/REP/pages/4553015415 sent by the employer to the Tax Office for reporting on US taxes paid by the employees for the entire year, and also “W2 form” printed and given to each employee /wiki/spaces/REP/pages/3282340058 .

View Sample

Main Data Selection

The view is selecting the value of accumulators that collect the different US tax basis and taxes throughout a year. It shows the total tax basis and tax for the year, it can also provide a split of amounts by quarters, if the field “Quarter” is used in the setup of the view.

SQL statement

select 
P01.PIN, P01.CLIENT NUMORGID, P01.EMPLOYMENTSTARTDATE, P01.EMPLOYMENTENDDATE,
employer.name as 'Union',
p01.pin as EmplID, 
CASE WHEN RIGHT(P0C.PERIODE,2) BETWEEN 01 AND 06 THEN 'Q1' 
	WHEN RIGHT(P0C.PERIODE,2) BETWEEN 06 AND 12 THEN 'Q2'
	WHEN RIGHT(P0C.PERIODE,2) BETWEEN 13 AND 16	THEN 'Q3'
	WHEN RIGHT(P0C.PERIODE,2) BETWEEN 17 AND 24	THEN 'Q4'
ELSE 'N/A' END AS 'QUARTER',
p0c.PERIODE,
p0y.SSN, 
Cast(Left(Cast(p0c.PERIODE as varchar),4) as int) [Tax Year],
p0c.ACC02 as [Fed Tax],
p0c.acc25 as [Fed Wage],
p0c.ACC03 AS [FICA TAX],
p0c.ACC26 as [FICA Wage],
p0c.ACC04 as [MED Tax],
p0c.ACC27 as [MED Wage],
p0c.ACC05 as [Pre-tax 401k],
p0c.ACC10 as [Pre-tax CatchUp 401k],
p0c.ACC07 as [Roth 401k],
p01.NAME,
p01.ADDRESS1,
p01.ADDRESS2,
p01.postplace as City,
post.COUNTYCODE as [State],
p01.postcode as [ZIP],
p01.address_country as Country

  from pw001p01 p01
left join PW001P0Y p0y on p0y.pin=p01.pin
left join pworg employer on employer.numorgid=p01.client
left join (select NUMORGID,PERIODE from PWORGCMP) payroll on employer.NUMORGID = payroll.NUMORGID
left join PW001P0C p0c on p0c.pin=p01.pin and p0c.PERIODE = (select max(t.PERIODE)
									from PW001P0C t 
									where t.PIN = p01.PIN
									and t.PERIODE <= payroll.PERIODE
									and t.PERIODE <= 202224)
LEFT JOIN pwpost post 
	ON post.postcode = p01.postcode 
	AND p01.postplace = post.NAME 
	AND p01.address_country = post.countrycode 

where p0y.AUTOTAX=1 and (p0c.ACC03<>0 or p0c.ACC04<>0)

Rows/ Columns Specification

Field name	Specification	Other info
Fed Tax	ACC02	US Federal Income Tax paid by employee
Fed Wage	ACC25	US Federal Income Tax basis, i.e. taxable wage of employee
FICA Tax	ACC03	FICA Tax paid by employee
FICA Wage	ACC26	FICA Tax basis, i.e. FICA taxable wage of employee
Med Tax	ACC04	Medicare Tax paid by employee
Med Wage	ACC27	Medicare Tax basis, i.e. Medicare taxable wage of employee
Pre-tax 401k	ACC05	Tax paid when employee is a member of 401k pension program
Pre-tax CatchUp 401k	ACC10	Tax paid when employee is a member of 401k pension program
Roth 401k	ACC07	Tax paid when employee is a member of 401k pension program

Other

A separate analytical view is created for each reporting year by updating the end period in the view script:

W2 Raw Data