Ah, ok. That's not going to work. Is there a date field in the database for each record?
If not, you'll have to do it in your output after all I think. w -----Original Message----- From: Lorenzo Watts [mailto:[email protected]] Sent: 30 January 2013 18:33 To: cf-newbie Subject: Re: <cfoutput> query results I'm using MsSql and the data type for DocFiscalYear is varChar >WHERE (DocAdmin_Users.UserUserID = <cfqueryparam value="#cfcUserUserID#" >cfsqltype="cf_sql_varchar">) AND (DocAdmin_MasterList.DocType = >'Budget') AND (BudgetApproval.BudgetStatusID = 4) AND >Year(DocAdmin_FiscalYear.DocFiscalYear) >= >Year(DateAdd("yyyy",-1,now())) > >Off the top of my head. > >Lots of joins in there though. That should work for MySQL, other DBs >the syntax might be slightly different. What datatype is that >DocFiscalYear column? I'm assuming it's a date, in which case >Year(DocAdmin_FiscalYear.DocFiscalYear) takes the year part of the date >in MySQL. > >Year(DateAdd("yyyy",-1,now()) says take the date now, remove a year >from it, and then just look at the year part of that. > >Will > >Thanks Will for the quick response. I thought that I would probably >need to do this in the query and at the time couldn't find it. I have >since found the function and query (see below). Where and how do I go >about adding the Where clause you suggested and can there be a third >"and" in the where clause. > ><cffunction name="getUserBudgets" output="false" description="Returns a >query with the users documents" access="remote" returntype="query"> > <cfargument name="cfcUserUserID" type="string" required="yes"> > <cfargument name="cfcDocID" type="string" required="no"> > <cfquery name="getUserDoc" datasource="HeartBeat"> > SELECT DocAdmin_MasterList.DocID, >DocAdmin_MasterList.DocType, DocAdmin_DocType.sortorder AS >DocType_SortOrder, DocAdmin_MasterList.DocTitle, > DocAdmin_MasterList.DocDueDate, >DocAdmin_MasterList.DocFiscalYear, DocAdmin_MasterList.DocDateCreated >AS DateCreated, > DocAdmin_MasterList.DocLastModified AS >LastModified, DocAdmin_MasterList.DocLockDate AS LockDate, DocAdmin_FiscalYear.Active, > DocAdmin_FiscalYear.SortOrder AS >FiscalYear_SortOrder, DocAdmin_Access.UserID AS UserId, DocAdmin_Users.UserUserID AS UserUserId, > DocAdmin_Department.DeptID, >DocAdmin_Department.DeptCode, DocAdmin_Department.DeptName, >DocAdmin_Department.DeptDetailCode, > BudgetApproval.BudgetStatusID >FROM DocAdmin_MasterList INNER JOIN > DocAdmin_DocType ON DocAdmin_MasterList.DocType = >DocAdmin_DocType.DocType INNER JOIN > DocAdmin_FiscalYear ON >DocAdmin_MasterList.DocFiscalYear = DocAdmin_FiscalYear.DocFiscalYear >INNER JOIN > DocAdmin_Access ON DocAdmin_MasterList.DocID = >DocAdmin_Access.DocID INNER JOIN > DocAdmin_Users ON DocAdmin_Access.UserID = >DocAdmin_Users.UserID INNER JOIN > DocAdmin_Department ON DocAdmin_MasterList.DeptID >= DocAdmin_Department.DeptID INNER JOIN > BudgetApproval ON DocAdmin_Department.DeptID = >BudgetApproval.BudgetApprovalDepID >WHERE (DocAdmin_Users.UserUserID = <cfqueryparam value="#cfcUserUserID#" >cfsqltype="cf_sql_varchar">) AND (DocAdmin_MasterList.DocType = >'Budget') AND (BudgetApproval.BudgetStatusID = 4) > ORDER BY >DocAdmin_FiscalYear.SortOrder, DocAdmin_DocType.sortorder > </cfquery> > <cfreturn getUserDoc /> ></cffunction> > >Thanks, Lorenzo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:6051 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
