i think i see what you're trying to do, but why the
seperate queries?
just do one query and forget the empcount vars and do
something like this
<cfloop query="qGetPresentEmployment">
<cfset row=qGetPresentEmployment.currentrow>
<cfif row is not 1>
<cfif
datecompare(dateadd('m',3,qGetPresentEmployment.todate[row-1]),qGetPresentEmployment.fromdate)
lt 0>
<cfset dogap=1>
</cfif>
</cfif>
</cfloop>
that's just the logic, and you need to check the
datecompare function to make sure i'm checking the
result right.. i forget.. it outputs 1,0,-1 depending
on the lt,eq,gt relationship between the two args.
anyway, that SHOULD work if i understood ya right.
if you need to put two queries into one, you can do
that using the query functions..
l8r
--- Greg Luce <[EMAIL PROTECTED]> wrote:
> 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
