1) Below is the complete code for the query page.
2) Go to www.markleder.com/query.cfm
There are two results screenshots:
Shot 1 = the results from this query
Shot 2 = a detail zoom for one of the members (this is included for
clarification), in this case, I'm getting duplicates, I only want to show
the most resent transactionID (eg, transaction date), not paid thru date.
The most recent transaction ID is the one with the paid thru as 7/31/2004,
not 12/31/2004. So, on Shot 1, the query results should show the paid
through for this individual as 7/31/2004, not 12/31/2004 as what is occuring
now
HTH
===============================================
<cfprocessingdirective pageencoding="utf-8">
<!--- If user is returning from adding, editing or deleting subscribers,
remove the cached query --->
<cfif URL.kc EQ 1>
<cfobjectcache action=""> </cfif>
<cfset VARIABLES.todaysDate = #CreateODBCDate(Now())#>
<cfparam name="URL.process" default="">
<cfparam name="SESSION.memberList.fieldSelect" type="string" default="All
Fields">
<cfparam name="SESSION.memberList.searchModifier" type="string"
default="containing">
<cfparam name="SESSION.memberList.ffText" type="string" default="">
<cfparam name="SESSION.memberList.DateFrom" type="string" default="">
<cfparam name="SESSION.memberList.DateThru" type="string" default="">
<cfparam name="SESSION.memberList.filterC" default="All"><!--- Member Status
--->
<cfparam name="URL.sortOrder" type="string" default="M.lastName">
<cfparam name="URL.sortDir" type="string" default="ASC">
<!--- If the user is submitting the "search" form, --->
<!--- we'll make their submission be the criteria for rest of session --->
<cfif IsDefined("FORM.fieldnames")><!---ffText--->
<cfset SESSION.memberList.ffText = FORM.ffText>
<cfset SESSION.memberList.fieldSelect = FORM.fieldSelect>
<cfset SESSION.memberList.searchModifier = FORM.searchModifier>
<cfset SESSION.memberList.DateFrom = FORM.DateFrom>
<cfset SESSION.memberList.DateThru = FORM.DateThru>
<cfset SESSION.memberList.filterC = FORM.filterC>
<cfset SESSION.memberList.sortOrder = URL.sortOrder>
<cfset SESSION.memberList.sortDir = URL.sortDir>
</cfif>
<cfquery name="qmemberList" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#"
CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID
FROM #REQUEST.prefix#_Members_List M
WHERE M.memberID =
(SELECT TOP 1 T.transactionID, T.paidThru, T.transactionDate, T.memberID
FROM #REQUEST.prefix#_Members_TransactionLog T) AND
<!--- If the user provided a filter string, --->
<cfswitch _expression_="#SESSION.memberList.fieldSelect#">
<cfcase value="All Fields">
(M.firstName <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
OR M.lastName <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.email <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.company <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.city <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.zip <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>)
</cfcase>
<cfcase value="First Name">
AND M.firstname <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="Last Name">
AND M.lastname <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="Email address">
AND M.email <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="company">
AND M.company <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="city">
AND M.city <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="zip">
AND M.zip <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
</cfswitch>
<!--- Filter on Criteria C (member status) if provided (querySwitch
13)--->
<cfif SESSION.memberList.FilterC NEQ "All">
AND '#SESSION.memberList.FilterC#' = M.memberLevelID
</cfif>
<!--- AND M.memberID = T.memberID WHERE memberID = M.memberID GROUP BY
memberID
AND EXISTS (SELECT MAX(paidThru) AS paidThruDate, memberID FROM
#REQUEST.prefix#_Members_TransactionLog WHERE memberID = M.memberID GROUP BY
memberID) --->
<!--- Also filter on From date, if provided --->
<cfif SESSION.memberList.FilterC NEQ "G">
<cfif IsDate(SESSION.memberList.DateFrom)>
AND T.paidThru >= #CreateODBCDate(SESSION.memberList.DateFrom)#
</cfif>
<!--- Also filter on Through date, if provided --->
<cfif IsDate(SESSION.memberList.DateThru)>
AND T.paidThru <= #CreateODBCDate(SESSION.memberList.DateThru)#
</cfif>
<cfelseif SESSION.memberList.FilterC EQ "G">
<cfif IsDate(SESSION.memberList.DateFrom)>
AND T.transactionDate >= #CreateODBCDate(SESSION.memberList.DateFrom)#
</cfif>
<!--- Also filter on Through date, if provided --->
<cfif IsDate(SESSION.memberList.DateThru)>
AND T.transactionDate <= #CreateODBCDate(SESSION.memberList.DateThru)#
</cfif>
</cfif>
GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email,
M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate,
T.memberID
<!---- Determines the sort column of the output
ORDER BY #URL.sortOrder# #URL.sortDIR# --->
</cfquery>
_____
From: Alexander Sherwood [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 11:20 AM
To: CF-Talk
Subject: RE: SQL Query problem
At 11:08 AM 8/18/2004, you wrote:
>OK, you asked for it :o) -- the select statement was my latest try at
>retrieving just the highest numbered transaction ID and corresponding data
>for each member. FYI - the filtering statements are only invoked after
>someone does a new search from a form. The inital results, which is what I
>am after, uses the <cfcase value = "all fields">. The default
URL.sortorder
>is "M.lastname" and URL.sortDir is "ASC".
>
>
>Thanks for helping me with this.
Can you post the actual, parsed SQL that is being executed against your DB?
This will be a little easier to troubleshoot with all of the variables and
such filled in...
Thanks!
--
Ale
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
