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:6050
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
