I'm not sure how efficient this might be but something like this should work...
<cfset startDate = createDate(yy,mm,dd)>
<cfsert endDate = dateAdd('d',1,startDate)>
<cfquery ...>
select u.emailaddress, u.firstname, u.surname
from user
where rec_id NOT in
(
select distinct bdi_usr_id
from body_diaries
where entrydate > startDate
and entryDate < endDate
)
</cfquery>
Cheers,
Brett
B)
Seona Bellamy wrote:
> Hi all!
>
> Have a system that people are supposed to log into every day and make
> an entry. I've been asked to created a scheduled task that every
> evening will look for all of the users who haven't made an entry today
> and send them a reminder email, but I'm having trouble getting me head
> around how to get that information.
>
> I have the following query, called "qReminders", that gets all of the entries:
>
> SELECT u.emailaddress, u.firstname, u.surname, u.rec_id,
> bde.bde_entrydate
> FROM users u
> INNER JOIN body_diaries bd ON u.rec_id = bd.bdi_usr_id
> INNER JOIN body_diary_entries bde ON bd.bdi_id
> = bde.bde_bdi_id
> WHERE u.user_level = 1
> ORDER BY u.rec_id, bde.bde_entrydate
>
> So then I thought I'd do a query-of-query on this one to get the
> details of all the people who haven't made an entry today, but I can't
> figure out what the filtering clauses would be. Just getting the
> records that aren't today's won't work. I need a way to say "where
> bde_entrydate does not include today's date in one of the records
> returned for this user".
>
> Is this even a good way to go about it? I'm concerned that doing an
> initial query that gets everything could become quite stupidly large
> over time.
>
> Cheers,
>
> Seona.
>
> >
>
>
--
Brett Payne-Rhodes
Eaglehawk Computing
t: +61 (0)8 9371-0471
m: +61 (0)414 371 047
e: [EMAIL PROTECTED]
w: http://www.yoursite.net.au
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~----------~----~----~----~------~----~------~--~---
