|
|
 |
|
|
|
|
 |
|
|
|
|
|
|
H-1B Prevailing Wage Claims vs. Reality:
What They Say, Versus What They Pay
|
|
|
By Robert Hill, CUNY, Murphy Institute for Worker Education
|
|
|
Appendix A: Data Acquisition Details
|
|
|
|
The text box below provides the standard Structured Query Language (SQL) statement behind the query for a typical SOC code. These queries varied ONLY in the variables in the SOC_CODE names, e.g. “15-1031” vs. “15-1051”. It is provided here to make it possible for others to replicate this work in the future.
|
|
|
|
SELECT H1B_FY2010.STATUS, H1B_FY2010.FULL_TIME_POS, H1B_FY2010.LCA_CASE_SOC_CODE, H1B_FY2010.LCA_CASE_SOC_NAME, H1B_FY2010.LCA_CASE_JOB_TITLE, H1B_FY2010.TOTAL_WORKERS, H1B_FY2010.LCA_CASE_WAGE_RATE_FROM, H1B_FY2010.LCA_CASE_WAGE_RATE_TO, H1B_FY2010.LCA_CASE_WAGE_RATE_UNIT, H1B_FY2010.PW_1, H1B_FY2010.PW_UNIT_1, H1B_FY2010.PW_SOURCE_1, H1B_FY2010.PW_2, H1B_FY2010.PW_UNIT_2, H1B_FY2010.PW_SOURCE_2, H1B_FY2010.LCA_CASE_WORKLOC1_CITY, H1B_FY2010.LCA_CASE_WORKLOC1_STATE
FROM H1B_FY2010
WHERE (((H1B_FY2010.STATUS)="CERTIFIED") AND ((H1B_FY2010.FULL_TIME_POS)="Y") AND ((H1B_FY2010.LCA_CASE_SOC_CODE) Like "15-101*") AND ((H1B_FY2010.LCA_CASE_WAGE_RATE_UNIT)="Year") AND ((H1B_FY2010.PW_UNIT_1)="Year") AND ((H1B_FY2010.PW_SOURCE_1)="OES") AND ((H1B_FY2010.PW_SOURCE_2) Is Null Or (H1B_FY2010.PW_SOURCE_2)="OES"));
|
|
|
|
A layman’s description of this query is as follows.
- Display the records in the table H1B_FY2010 with the following specifications:
- Display only the records where the STATUS field is “CERTIFIED,” the FULL_TIME_POS field is “Y” for yes, and the LCA_CASE_SOC_CODE is “Like 15-1031*”. The use of a wildcard “*” character is necessary because some of the records have a shorter form of the SOC code and some have a longer, e.g. “15-1031” and “15-1031.00.” The use of the wildcard character in the query string allows both forms to be returned.
- Also, only display records where the LCA_CASE_WAGE_RATE_UNIT is “Year.” This has the effect of filtering out those that are based on “Hourly” or “Weekly” wage rates. The vast majority of the LCAs are reported in terms of yearly wages.
- Similarly, also only display records where the PW_UNIT_1 and PW_UNIT_2 is “Year.” If the LCA wage is reported as a yearly unit, then the prevailing wage unit must also be reported in yearly terms. This prevents contamination of the sample where LCA wages are reported yearly but prevailing wages are reported hourly, for instance.
- Similarly, also only display records where the PW_SOURCE_1 is “OES” and the PW_SOURCE_2 is either (null), meaning there’s nothing there, or “OES.” This prevents contamination of the sample where the prevailing wage source is other than the OES online wage library. The vast majority of the LCA records now use OES as the prevailing wage source.
- For those records that meet all of the above requirements, display the value stored in that field.
- For those records that meet all of the above requirements, also display the values stored in the LCA_CASE_SOC_NAME, LCA_CASE_JOB_TITLE, TOTAL_WORKERS, LCA_CASE_WAGE_RATE_FROM, LCA_CASE_WAGE_RATE_TO, PW_1, PW_2, LCA_CASE_WORKLOC1_CITY, and LCA_CASE_WORKLOC1_STATE.
For further clarification, the following table lists the fields that were drawn from the master table and exported to Excel for manipulation and study. The table lists the formal name of the field, the conditional requirement for selecting the field (if any), and a brief description of what the field is or means.
|
|
|
|
|
|
Table 11: Database Fields from OFLC Source .mdb File
|
|
Field Name
|
Required?
|
Description
|
|
STATUS
|
“CERTIFIED”
|
Certified, Denied, Withdrawn, or Pending.
|
|
FULL_TIME_POS
|
“Y”
|
“Y” for yes, “N” for no.
|
|
LCA_CASE_
SOC_CODE
|
Like
“15-1031*”
|
The SOC code. The formal code includes two digits to the right of the decimal place, but this is often left out. E.g., “15-1031.00” and “15-1031”.
|
|
LCA_CASE_
SOC_NAME
|
|
The official SOC code name.
|
|
LCA_CASE_
JOB_TITLE
|
|
The Job Title that was entered when the LCA was created. This varies with each LCA.
|
|
TOTAL_WORKERS
|
|
The total number of workers that are applied for on this single LCA. The vast majority of LCAs are for one worker, but the data includes some LCAs that were for 150+ workers.
|
|
LCA_CASE_
WAGE_RATE_FROM
|
|
In cases where there are multiple workers per LCA, employers often state a range of proposed salaries. This represents the lowest salary in that range.
|
|
LCA_CASE_
WAGE_RATE_TO
|
|
This represents the upper range of the salaries for a set of multiple workers on a single LCA. This field may be blank.
|
|
LCA_CASE_
WAGE_RATE_UNIT
|
“Year”
|
“Year,” “Hourly,” or “Weekly.” Records were only selected if this value was “Year”.
|
|
PW_1
|
|
The lower range of the prevailing wage claimed for this LCA. This is represented as a number, e.g. “86,000.”
|
|
PW_UNIT_1
|
“Year”
|
“Year,” “Hourly,” or “Weekly.” Records were only selected if this value was “Year”.
|
|
PW_SOURCE_1
|
“OES”
|
Indicates the source of the prevailing wage claim. This study is focusing on only those LCAs where the prevailing wage claimed was drawn from the OES data, which is virtually all of the LCAs for 2010.
|
|
PW_2
|
|
The upper range of the prevailing wage claimed for this LCA, if entered. This is represented as a number, e.g. “86,000.”
|
|
PW_UNIT_2
|
|
“Year,” “Hourly,” or “Weekly,” or (null).
|
|
PW_SOURCE_2
|
(null) or “OES”
|
For those records that contained a second source, it was possible that that source might be different from that for PW_1. This conditional query statement allows records to be chosen for display if the value of the field is (null) blank or “OES” only to prevent records that contain conflicting wage sources.
|
|
LCA_CASE_
WORKLOC1_CITY
|
|
The city where the work is to take place. There is a possible field for a second city, but that was not considered in this study.
|
|
LCA_CASE_
WORKLOC1_STATE
|
|
The state where the work is to take place. There is a possible field for a second state, but that information was not considered in this study.
|
|
|
