I'm getting fairly proficient with it, but this next step will require skills that I have not yet developed so I'm looking for advice.
There are 7 spreadsheets that have the appropriate visa data plus another that has the current year data. After studying it for some time I realized that I can get a true count of all visa holders that are currently in America by lumping all of these together in one table and then deleting all where the "work ending date" is older than the current date. This by itself will give me the most accurate data of any site out there, BUT. There is a very real possibility that a LCA is in more than 1 spreadsheet. So I need to de-dupe it. Theoretically I can say select distinct(LCA_number) and get just one record. But how do I grab the most current? (they do have a decision_date) (By the way, don't click on any of those spreadsheet links as they are at a minimum 80MB) LCA Programs (H-1B, H-1B1, E-3) Fiscal Year Disclosure File File Structure 2013 <http://www.foreignlaborcert.doleta.gov/docs/lca/LCA_FY2013.xlsx> LCA_FY2013.xlsx <http://www.foreignlaborcert.doleta.gov/docs/lca/LCA_Record_Layout_FY13.doc> LCA_Record_Layout_FY13.doc 2012 <http://www.foreignlaborcert.doleta.gov/docs/py2012_q4/LCA_FY2012_Q4.xlsx> LCA_FY2012.xlsx <http://www.foreignlaborcert.doleta.gov/docs/py2012_q4/LCA_Record_Layout_FY1 2.doc> LCA_Record_Layout_FY12.doc 2011 <http://www.foreignlaborcert.doleta.gov/docs/lca/H-1B_iCert_LCA_FY2011_Q4.xl sx> LCA_FY2011.xlsx <http://www.foreignlaborcert.doleta.gov/pdf/quarter_4_2011/H-1B_Record_Layou t_FY11_Q4.doc> LCA_Record_Layout_FY11.doc 2010 <http://www.foreignlaborcert.doleta.gov/docs/lca/H-1B_FY2010.xlsx> LCA_FY2010.xlsx <http://www.foreignlaborcert.doleta.gov/pdf/H-1B_Record_Layout_FY10.doc> LCA_Record_Layout_FY10.doc 2009 <http://www.foreignlaborcert.doleta.gov/docs/lca/Icert_%20LCA_%20FY2009.xlsx > LCA_iCert_FY2009.xlsx <http://www.foreignlaborcert.doleta.gov/pdf/H1B_Layout_FY09.doc> H-1B_Record_Layout_FY09.doc 2009(1) <http://www.foreignlaborcert.doleta.gov/docs/lca/H-1B_Case_Data_FY2009.xlsx> LCA_eFile_FY2009.xlsx <http://www.foreignlaborcert.doleta.gov/pdf/H-1B%20Efile%20Record%20Layout%2 0FY09.rtf> H-1B Efile Record Layout FY09.doc 2008 <http://www.foreignlaborcert.doleta.gov/docs/lca/H-1B_Case_Data_FY2008.xlsx> LCA_FY2008.xlsx <http://www.foreignlaborcert.doleta.gov/pdf/H-1B_Record_Layout_FY08.doc> H-1B_Record_Layout_FY08.doc Virgil Bierschwale www.VirgilBierschwale.com Keep America At Work www.KeepAmericaAtWork.com Where Are The Jobs? http://keepamericaatwork.com/?page_id=53 --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
