This seems like it is more complicated than it need be. Without knowing the
exact structure of your database, its tough to give you an exact solution.
First, I would guess that your bogus date of 01/01/1900 indicates that the
employee is still employed in that position? If so, try the code below:
Start Code---------------
<!--- First Get All The Jobs for the employee no matter when started or
ended --->
<Cfquery name="qGetAllEmployment" Datasource="aeps"
dbtype="#request.dbtype#" dbserver="#request.dbserver#">
SELECT *
from employment
where membernumber=#client.bgworkingon#
order by fromdate DESC
</cfquery>
<!--- Create 4 variables All that matters is previous to date--->
<cfset prevToDate = "">
<cfset lCount = 1>
<cfif qGetAllEmployment.todate IS "01/01/1900">
<cfset oAction = "Present">
<cfelse>
<cfset oAction = "NoGap">
</cfif>
<cfset GapLen = 0>
<!--- All the output you need can be done with a single output --->
<cfoutput query="qGetAllEmployment">
<!--- Check the gap variable --->
<cfif qGetAllEmployment.CurrentRow NEQ 1>
<!--- Do calculations here - if from - previous todate is GT 3, set oAction
= Gap --->
<cfif datediff("m", #qGetAllEmployment.fromdate#, #prevToDate#) gt 3>
<cfset oAction = "Gap">
<cfelse>
<cfset oAction = "NoGap">
</cfif>
</cfif>
<!--- Do your Ouput --->
<cfswitch expression=#oAction#>
<cfcase value="Present">
#qGetAllEmployment.CurrentRow#.
#dateformat(qGetAllEmployment.fromdate,
"mm/dd/yyyy")# - Present<br>
</cfcase>
<cfcase value="NoGap">
#qGetAllEmployment.CurrentRow#.
#dateformat(qGetAllEmployment.fromdate,
"mm/dd/yyyy")# - #dateformat(qGetAllEmployment.todate, "mm/dd/yyyy")#<br>
</cfcase>
<cfcase value="Gap">
Gap In Employment<br><!--- You could get tricky and calulate actual
gap in
the above if and assign it to GapLen --->
#qGetAllEmployment.CurrentRow#.
#dateformat(qGetAllEmployment.fromdate,
"mm/dd/yyyy")# - #dateformat(qGetAllEmployment.todate, "mm/dd/yyyy")#<br>
</cfcase>
</cfswitch>
<!--- Now set and calculate --->
<cfset prevToDate = "#qGetAllEmployment.todate#">
</cfoutput>
End Code -----------------
I may need a few conversions between the string and date in the comparison
but otherwise the logic should be right. Obviously I have no way of testing
it with your data. The above code is much simpler and only uses one output
and no loops. I hope this simplifies things for you.
Jim Nitterauer
http://www.creativedata.net
-----Original Message-----
From: Greg Luce [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 17, 2001 4:57 PM
To: Fusebox
Subject: Looping Nightmare!
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
