If anyone can take a peek at this and see what's wrong. I'm trying to fetch
employment history records from 2 different queries, then display them. And
then the tricky part, if there is a gap of 3 months or more after the last
record (and not "to present") or between 2 employment records, show that.
These are the results I'm getting right now, but they are wrong. There
should be a gap between the second and third records, and the one between 3
and 4 should display "Gap In Employment", not the variable name. :-) I have
a little trouble with the evaluate() function. Thanks for any insight.
5
0
34
57
Gap In Employment
1. 11/01/2000 - 11/01/2000
2. 02/01/1998 - 11/01/2000
3. 03/01/1994 - 04/01/1995
gap3
4. 05/01/1989 - 06/01/1989
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
