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