Here's the code:

<!--- This code pulls a Members "to present" employment records first. --->

<cfset empcount = 1>

<Cfquery name="qGetPresentEmployment" Datasource="aeps" dbtype="#request.dbtype#" dbserver="#request.dbserver#"> SELECT * from employment where membernumber=#client.bgworkingon# and todate = '01/01/1900' order by fromdate DESC </cfquery> <cfif qgetpresentemployment.recordcount gt 0><!--- If there are "To Present" records. ---> <cfloop query="qgetpresentemployment"><!--- Loop thru the records setting variables for gap-comparison. ---> <cfoutput> <cfset "employer#empcount#fromdate" = #fromdate#> <cfset "employer#empcount#todate" = #todate#> <cfset empcount = empcount + 1><!--- Increment "empcount" ---> </cfoutput> </cfloop> </cfif> <!--- Next we pull the rest of the employment records that are NOT "To Present". ---> <Cfquery name="qGetEmployment" Datasource="aeps" dbtype="#request.dbtype#" dbserver="#request.dbserver#"> SELECT * from employment where membernumber=#client.bgworkingon# and todate <> '01/01/1900' order by todate DESC </cfquery> <cfif qgetemployment.recordcount gt 0> <!--- If there are employment records that're NOT "To Present". ---> <cfloop query="qgetemployment"> <!--- Loop throught them setting variables for gap-comparison. ---> <cfoutput> <cfset "employer#empcount#fromdate" = #fromdate#> <cfset "employer#empcount#todate" = #todate#> <cfif empcount gt 1> <!--- If there were "To Present" records, or it's NOT the first time through this loop. ---> <cfset x = empcount - 1> <!--- Set x = to the previous record's empcount ---> <cfset comparefrom = "#evaluate("employer#evaluate("x")#fromdate")#"> <!--- Set "comparefrom = previous record's "fromdate". ---> <cfelse> <!--- If there aren't "To Present records and this is our first time through this loop.---> <cfset comparefrom = "#now()#"> </cfif> <!--- Display Comparison of dates to see if there was a gap in employment. (Just for testing) ---> #datediff("m", evaluate("todate"), evaluate("comparefrom"))#<br> <!--- compare todate with either today's date or the previous record's "fromdate to see if there was a gap in employment greater than 3 months. ---> <cfif datediff("m", #todate#, #comparefrom#) gt 3> <cfset "gap#empcount#" = "Gap In Employment"><!--- Set a unique "Gap" variable if applicable. ---> <cfelse> <cfset "gap#empcount#" = ""> </cfif>

<cfset empcount = empcount + 1> </cfoutput> </cfloop> </cfif> <!--- End "fetching" loop. --->

<cfoutput> <!--- Loop through all "fetched" employment records. ---> <cfset tocount = qgetpresentemployment.recordcount+qgetemployment.recordcount> <cfloop index="currentrow" from="1" to="#tocount#"> <!--- If it's the first record to display and it was calculated earlier that there's a gap in employment, display it. ---> <cfif currentrow eq 1 and len(evaluate("gap#currentrow#")) gt 1> #evaluate("gap#currentrow#")#<br> </cfif> <cfset fromdate = "#evaluate("employer#evaluate("currentrow")#fromdate")#"> <cfset todate = "#evaluate("employer#evaluate("currentrow")#todate")#"> <!--- Display the rownumber, the fromdate, and the todate. ---> #currentrow#. #dateformat(fromdate, "mm/dd/yyyy")# - #dateformat(todate, "mm/dd/yyyy")#<br> <!--- If it's not a "to present" record, and it's not the last record in the list. ---> <cfif currentrow gt qgetpresentemployment.recordcount and currentrow lt tocount> <!--- If it was calculated earlier that there's a "gap" for this record, display it. ---> <cfif currentrow gt 1 and len(evaluate("gap#currentrow#")) gt 0> #trim("gap#currentrow#")#<br> </cfif> </cfif> </cfloop> </cfoutput><cfabort>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/fusebox@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists