RE: Systdate Query
try modifying it to this: datetaken in (select datetaken from cim_educationalexp where userid='#client.cim_userid#') instead of datetaken = (max(...)...) -Original Message- From: Rayna Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 9:41 AM To: CF-Talk Subject: Systdate Query Hi all, I am trying to pull up all data entered for a particular day (yesterday) in my output page. Here is the query: cfquery name=getEducation datasource=#variables.cimdata# SELECT classenj, course, why, datetaken FROM cim_educationalexp WHERE userid='#client.cim_userid#' AND datetaken = (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#') /cfquery My question: is there anyway to pull all the data entered for that date? In the subquery (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#'), I have MAX for the datetaken variable (or sysdate), but what i am trying to do is get all the data entered for that day. what i am getting is the last data entered for that day. There are 4 other enteries that are from that day and I can't get them to come up. Any suggestions? Thanks, Rayna Evans Rayna Evans AAMC 2501 M Street, 2nd Fl Washington, DC 20037 202-862-6243 (direct) Extension 4243 (internal) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Systdate Query
Oops. Misread your message last time... Instead, try this: and datetaken between '01/01/2001 00:00:00' AND '01/01/2001 23:59:59' (substitute date where appropriate) - this will find everything for the first of this year. i don't know of a way to do this in your subquery, however... you'll have to know the date you're searching on ahead of time rather than doing a max(..) in the same query. -Original Message- From: Rayna Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 9:41 AM To: CF-Talk Subject: Systdate Query Hi all, I am trying to pull up all data entered for a particular day (yesterday) in my output page. Here is the query: cfquery name=getEducation datasource=#variables.cimdata# SELECT classenj, course, why, datetaken FROM cim_educationalexp WHERE userid='#client.cim_userid#' AND datetaken = (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#') /cfquery My question: is there anyway to pull all the data entered for that date? In the subquery (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#'), I have MAX for the datetaken variable (or sysdate), but what i am trying to do is get all the data entered for that day. what i am getting is the last data entered for that day. There are 4 other enteries that are from that day and I can't get them to come up. Any suggestions? Thanks, Rayna Evans Rayna Evans AAMC 2501 M Street, 2nd Fl Washington, DC 20037 202-862-6243 (direct) Extension 4243 (internal) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Systdate Query
Thanks Dave, but it's still not quite there. Here is the scenario...maybe this will make it more clear. I have developed an application that involves a bunch of tests...when the user completes one of the tests, their results will be displayed on a profile page, there is a hyperlink on the title of each test on the profile page which will take the user to a profile summary page. The profile page just gives the user the results of the test taken on the most recent date (if they took a test twice on the same day it should display all their results on the profile page). If they chose the hyperlinked title on the profile page, they will go into the summary page and see all the results of their previous test results on the summary page (which gives them a view of what results they got during other dates they took the test). Hope that makes sense. Now, I had it working right the other day...but since I am a newbie to Cold fusion, I don't know how I did it and I Have tried every variation with my SQL statement...it's like I am almost there, but not quite. So, if I don't use the datetaken subquery in my statement...EVERYTHING displays on the profile page and i just need all the results from the most recent date...all the other results from the other dates should display on the summary page. I am about to jump out the window on this one :-( Rayna Rayna Evans AAMC 2501 M Street, 2nd Fl Washington, DC 20037 202-862-6243 (direct) Extension 4243 (internal) [EMAIL PROTECTED] 08/30/01 07:58AM Oops. Misread your message last time... Instead, try this: and datetaken between '01/01/2001 00:00:00' AND '01/01/2001 23:59:59' (substitute date where appropriate) - this will find everything for the first of this year. i don't know of a way to do this in your subquery, however... you'll have to know the date you're searching on ahead of time rather than doing a max(..) in the same query. -Original Message- From: Rayna Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 9:41 AM To: CF-Talk Subject: Systdate Query Hi all, I am trying to pull up all data entered for a particular day (yesterday) in my output page. Here is the query: cfquery name=getEducation datasource=#variables.cimdata# SELECT classenj, course, why, datetaken FROM cim_educationalexp WHERE userid='#client.cim_userid#' AND datetaken = (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#') /cfquery My question: is there anyway to pull all the data entered for that date? In the subquery (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#'), I have MAX for the datetaken variable (or sysdate), but what i am trying to do is get all the data entered for that day. what i am getting is the last data entered for that day. There are 4 other enteries that are from that day and I can't get them to come up. Any suggestions? Thanks, Rayna Evans Rayna Evans AAMC 2501 M Street, 2nd Fl Washington, DC 20037 202-862-6243 (direct) Extension 4243 (internal) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Systdate Query
This should work:
/**/
WHERE userid='#client.cim_userid#'
AND
TRUNC(dateTaken) = TRUNC(TO_DATE('29-8-2001','dd-mm-'))
AND TRUNC(dateTaken) = TRUNC(TO_DATE('29-8-2001','dd-mm-'))
/**/
making the date variable of course...
I know there is a better way, but off the top of the noggin this
should work.
cheers
-Rice
-Original Message-
From: Rayna Evans [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 10:22 AM
To: CF-Talk
Subject: RE: Systdate Query
Thanks Dave, but it's still not quite there. Here is the scenario...maybe
this will make it more clear.
I have developed an application that involves a bunch of tests...when the
user completes one of the tests, their results will be displayed on a
profile page, there is a hyperlink on the title of each test on the profile
page which will take the user to a profile summary page. The profile page
just gives the user the results of the test taken on the most recent date
(if they took a test twice on the same day it should display all their
results on the profile page). If they chose the hyperlinked title on the
profile page, they will go into the summary page and see all the results of
their previous test results on the summary page (which gives them a view of
what results they got during other dates they took the test). Hope that
makes sense.
Now, I had it working right the other day...but since I am a newbie to Cold
fusion, I don't know how I did it and I Have tried every variation with my
SQL statement...it's like I am almost there, but not quite. So, if I don't
use the datetaken subquery in my statement...EVERYTHING displays on the
profile page and i just need all the results from the most recent date...all
the other results from the other dates should display on the summary page.
I am about to jump out the window on this one :-(
Rayna
Rayna Evans
AAMC
2501 M Street, 2nd Fl
Washington, DC 20037
202-862-6243 (direct)
Extension 4243 (internal)
[EMAIL PROTECTED] 08/30/01 07:58AM
Oops. Misread your message last time...
Instead, try this:
and datetaken between '01/01/2001 00:00:00' AND '01/01/2001 23:59:59'
(substitute date where appropriate) - this will find everything for the
first of this year. i don't know of a way to do this in your subquery,
however... you'll have to know the date you're searching on ahead of time
rather than doing a max(..) in the same query.
-Original Message-
From: Rayna Evans [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 9:41 AM
To: CF-Talk
Subject: Systdate Query
Hi all,
I am trying to pull up all data entered for a particular day (yesterday) in
my output page. Here is the query:
cfquery name=getEducation datasource=#variables.cimdata#
SELECT classenj, course, why, datetaken
FROM cim_educationalexp
WHERE userid='#client.cim_userid#' AND datetaken = (select
MAX(datetaken)
from cim_educationalexp where userid='#client.cim_userid#')
/cfquery
My question: is there anyway to pull all the data entered for that date? In
the subquery (select MAX(datetaken) from cim_educationalexp where
userid='#client.cim_userid#'), I have MAX for the datetaken variable (or
sysdate), but what i am trying to do is get all the data entered for that
day. what i am getting is the last data entered for that day. There are 4
other enteries that are from that day and I can't get them to come up.
Any suggestions?
Thanks,
Rayna Evans
Rayna Evans
AAMC
2501 M Street, 2nd Fl
Washington, DC 20037
202-862-6243 (direct)
Extension 4243 (internal)
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Systdate Query
Use the between date1 and date1 that I showed you before. Here's how I would do it: cfquery name=getDate datasource=#variables.cimdata# select MAX(datetaken) AS Date from cim_educationalexp where userid='#client.cim_userid#' /cfquery cfif getDate.RecordCount GT 0 cfquery name=getEducation datasource=#variables.cimdata# SELECT classenj, course, why, datetaken FROMcim_educationalexp WHERE userid='#client.cim_userid#' AND datetaken BETWEEN '#DateFormat(getDate.Date, 'mm/dd/')# 00:00:00' AND #DateFormat(getDate.Date, 'mm/dd/')# 23:59:59' /cfquery !--- do output --- cfelse No test results. /cfif It's more verbose than doing it in one query, but IMHO it's much cleaner. -Original Message- From: Rayna Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 10:22 AM To: CF-Talk Subject: RE: Systdate Query Thanks Dave, but it's still not quite there. Here is the scenario...maybe this will make it more clear. I have developed an application that involves a bunch of tests...when the user completes one of the tests, their results will be displayed on a profile page, there is a hyperlink on the title of each test on the profile page which will take the user to a profile summary page. The profile page just gives the user the results of the test taken on the most recent date (if they took a test twice on the same day it should display all their results on the profile page). If they chose the hyperlinked title on the profile page, they will go into the summary page and see all the results of their previous test results on the summary page (which gives them a view of what results they got during other dates they took the test). Hope that makes sense. Now, I had it working right the other day...but since I am a newbie to Cold fusion, I don't know how I did it and I Have tried every variation with my SQL statement...it's like I am almost there, but not quite. So, if I don't use the datetaken subquery in my statement...EVERYTHING displays on the profile page and i just need all the results from the most recent date...all the other results from the other dates should display on the summary page. I am about to jump out the window on this one :-( Rayna Rayna Evans AAMC 2501 M Street, 2nd Fl Washington, DC 20037 202-862-6243 (direct) Extension 4243 (internal) [EMAIL PROTECTED] 08/30/01 07:58AM Oops. Misread your message last time... Instead, try this: and datetaken between '01/01/2001 00:00:00' AND '01/01/2001 23:59:59' (substitute date where appropriate) - this will find everything for the first of this year. i don't know of a way to do this in your subquery, however... you'll have to know the date you're searching on ahead of time rather than doing a max(..) in the same query. -Original Message- From: Rayna Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 9:41 AM To: CF-Talk Subject: Systdate Query Hi all, I am trying to pull up all data entered for a particular day (yesterday) in my output page. Here is the query: cfquery name=getEducation datasource=#variables.cimdata# SELECT classenj, course, why, datetaken FROM cim_educationalexp WHERE userid='#client.cim_userid#' AND datetaken = (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#') /cfquery My question: is there anyway to pull all the data entered for that date? In the subquery (select MAX(datetaken) from cim_educationalexp where userid='#client.cim_userid#'), I have MAX for the datetaken variable (or sysdate), but what i am trying to do is get all the data entered for that day. what i am getting is the last data entered for that day. There are 4 other enteries that are from that day and I can't get them to come up. Any suggestions? Thanks, Rayna Evans Rayna Evans AAMC 2501 M Street, 2nd Fl Washington, DC 20037 202-862-6243 (direct) Extension 4243 (internal) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
