ok i have this query once the results are generated i can do this, below... but i would like to be able to do the same thing in the select query. so i can do some checks in my where statements.
how would i do the Years At Firm: & Total Years Experience: in a select query? hire date: #DateFormat(HIRE_DATE, "mm/dd/yyyy")# years other firms: <CFIF #YEARS_OTHER_FIRMS# IS ""><CFSET YEARS_OTHER_FIRMS = 0><CFELSE>#LSNumberFormat(YEARS_OTHER_FIRMS)#</cfif> Years At Firm: #YearsAtFirm# <CFSET TotalD = #Year(todayDate)# - #Year(set_Emp.HIRE_DATE)#> #TotalD# Total Years Experience: <CFSET TotalYearsEx1 = #LSNumberFormat(YEARS_OTHER_FIRMS)# + #TotalD#>#TotalYearsEx1# Thank You -paul ============================================================================ ======================== <cfquery name="set_Emp" datasource="rtwod2" dbtype="ODBC"> SELECT tblEmployee.EmployeeID, tblEmployee.StudioID, tblEmployee.TitleID, tblEmployee.PracticeGroupID, tblEmployee.FirstName, tblEmployee.LastName, tblEmployee.FloorID, tblEmployee.Email, tblEmployee.EmployeePhoto, tblEmployee.StatusID, tblEmployee.HIRE_DATE, tblEmployee.YEARS_OTHER_FIRMS, tblStudio.StudioID, tblStudio.StudioName, tblStudio.OfficeID, tblOffice.OfficeID, tblOffice.City, tblJobTitle.TitleID, tblJobTitle.Title, tblFloor.Floor, tblPhone.PhoneID, tblPhone.PhoneTypeID, tblPhone.PhoneNumb, tblEmplDegree.EMPLOYEE_DEGREE, tblEmplDegree.EMPLOYEE_ID, tblEmplDegree.INSTITUTION, tblEmplDegree.YEAR_DEGREE_EARNED, tblEmplDegree.DEGREE_SPECIALTY, tblEmplDiscipline.DISCIPLINE_NAME_EMPL, tblEmplRegistration.DATE_EARNED_REGISTRATION, tblEmplRegistration.STATE_REGISTERED FROM tblEmployee, tblStudio, tblOffice, tblJobTitle, tblFloor, tblPhone, tblEmplDegree,tblEmplDiscipline, tblEmplRegistration WHERE tblEmployee.StatusID <> 0 AND tblEmployee.StudioID = tblStudio.StudioID AND tblEmployee.TitleID = tblJobTitle.TitleID AND tblEmployee.FloorID = tblFloor.FloorID AND tblEmployee.EmployeeID = tblPhone.EmpResID AND tblStudio.OfficeID = tblOffice.OfficeID AND tblPhone.PhoneTypeID = '1' <!--- Filter by StudioID if needed ---> <CFIF Trim(FORM.StudioID) IS NOT "" and Trim(FORM.StudioID) is not 0> AND tblEmployee.StudioID = #LSParseNumber(FORM.StudioID)# </CFIF> <!--- Filter by EMPLOYEE_DEGREE if needed ---> <CFIF Trim(FORM.EMPLOYEE_DEGREE) IS NOT ""> AND tblEmplDegree.EMPLOYEE_ID=tblEmployee.EmployeeID AND EMPLOYEE_DEGREE LIKE '%#Trim(FORM.EMPLOYEE_DEGREE)#%' </CFIF> <!--- Filter by INSTITUTION if needed ---> <CFIF Trim(FORM.INSTITUTION) IS NOT ""> AND tblEmplDegree.EMPLOYEE_ID=tblEmployee.EmployeeID AND INSTITUTION LIKE '%#Trim(FORM.INSTITUTION)#%' </CFIF> <!--- Filter by DEGREE_SPECIALTY if needed ---> <CFIF Trim(FORM.DEGREE_SPECIALTY) IS NOT ""> AND tblEmplDegree.EMPLOYEE_ID=tblEmployee.EmployeeID AND DEGREE_SPECIALTY LIKE '%#Trim(FORM.DEGREE_SPECIALTY)#%' </CFIF> <!--- Filter by YEAR_DEGREE_EARNED if needed ---> <CFIF Trim(FORM.S_year_degree_earned) IS NOT ""> AND tblEmplDegree.EMPLOYEE_ID=tblEmployee.EmployeeID AND YEAR_DEGREE_EARNED BETWEEN #Year(FORM.S_year_degree_earned)# AND #Year(FORM.E_year_degree_earned)# </CFIF> <!--- Filter by Phone if needed ---> <CFIF Trim(FORM.Phone) IS NOT ""> AND (tblPhone.PhoneNumb LIKE '%#Trim(FORM.Phone)#%') </CFIF> ORDER BY LastName;</cfquery> ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
