I think this should work for you (hard to tell with out understanding
exactly what you wanted to achieve :))
<cfquery name="qs_GetResultEmail" datasource="#dsn#">
select r.rid, r.ocid, r.email, r.date,
t.followUpEmailID, t.customerID,
t.followUpEmailDelay, t.followUpEmailSubject,
t.followUpEmailMessage, t.followUpEmailTitle
from results r, tblFollowUpEmails t
where r.email <> 'remove'
and r.ocid = t.ocid
and date = dateAdd(dd, -followUpEmailDelay,
getDate())
</cfquery>
You probably don't need all the selected columns, but I've just put in
the ones you had already.
Hth,
James
-----Original Message-----
From: Paul Cook [mailto:[EMAIL PROTECTED]]
Sent: Friday, 11 October 2002 11:31
To: CF-Talk
Subject: Need Help Writing a Query
Hey Im hoping somone here can help me out...
Heres what Im trying to do:
I have in the Results table the email addresses of prospects and the
dates they signeed onto the list, In tblFollowUpEmails I have a message
that will go out so many days (followUpEmailDelay) after signing up
what im getting at is can someone help me put this together into one
query that will pull out the results based on the automated responce
that they should get for that day.
Below has the logic, but how can I make this work as one query any help
is apretiated...
Thanks
-Pual
<cfquery name="qs_GetFollowUps" datasource="#dsn#">
SELECT FollowUpEmailID, CustomerID, FollowUpEmailDelay,
FollowUpEmailSubject, FollowUpEmailMessage, FollowUpEmailTitle, ocid
FROM tblFollowUpEmails
</cfquery>
<cfoutput query="qs_GetFollowUps">
<cfset adjustedDate=#DateAdd("d", -#FollowUpEmailDelay#,
#DateFormat(Now())#)#> <cfquery name="qs_GetResultEmail"
datasource="#dsn#">
SELECT rid, ocid, email, date
FROM Results
WHERE (Email <> 'remove') and (ocid='#ocid#') and
(date=#createodbcdate(adjustedDate)#)
</cfquery>
</cfoutput>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
