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 <gregl@aeps.com> 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/fusebox@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists