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
-----Original Message-----
From: Lorenzo Watts [mailto:[email protected]]
Sent: 30 January 2013 18:10
To: cf-newbie
Subject: Re: <cfoutput> query results
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:6049
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
