Thanks. That has been the trait that let me go from a raw ranching community kid to a technology based project manager before everything went to hell on me.
Spent the day converting and uploading the years from 2009 till current visa data and I think you guys and gals will be extremely shocked by what I found when I lumped it all together and got rid of those that had finished working. You can read a summary of it here. http://keepamericaatwork.com/?p=243 Fair warning - You better be sitting down when you read it. Or you can view real data by clicking here. http://keepamericaatwork.com/?page_id=53 Now if I just had 40 or 50 bucks, I think I would get a case of miller lite, and some meat to burn and try and destress... But since I only have 50 cents, THAT ain't going to happen <grin> On a side note, it took me a month to figure out how to get a 442,000 record spreadsheet into a mysql hosted table that I did not have access too. Today I did 6 before lunch and it is a time consuming process when you factor in downloading, converting, uploading. -----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, September 26, 2014 3:13 PM To: ProFox Email List Subject: RE: [NF] Any MySQL experts out there? You're like a bloodhound who won't let go, Virgil. No one can question your dedication to what you believe in! On 2014-09-26 15:34, Virgil Bierschwale wrote: > Yep, but I got it all put together now. > > That is what I ended up doing. > > > > -----Original Message----- > From: ProFox [mailto:[email protected]] On Behalf Of > [email protected] > Sent: Friday, September 26, 2014 2:36 PM > To: ProFox Email List > Subject: Re: [NF] Any MySQL experts out there? > > Your issue sounds like a basic SQL issue, not specific to MySQL. Is > this for the Keep America At Work site? > > select * from YourTable where EndingDate >= current_date() > > > > On 2014-09-26 10:02, Virgil Bierschwale wrote: >> 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_FY1 > 3.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_Layo > ut_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.d >> oc> >> LCA_Record_Layout_FY10.doc >> >> >> 2009 >> >> >> > <http://www.foreignlaborcert.doleta.gov/docs/lca/Icert_%20LCA_%20FY200 > 9.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%20La > yout%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.d >> oc> >> 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 >> --- >> [excessive quoting removed by server] _______________________________________________ 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.
