SELECT re.*, rtl.*,
CAST(RIGHT(IsNull(PubNumber,'77-0'), LEN(IsNull(PubNumber,'77-0'))-3) as
int) as NumberWithinAYear
FROM Research re, ResearchTypesLookup rtl
WHERE re.ResearchTypeID=2
--AND re.ResearchYear=1998
AND re.ResearchTypeID=rtl.ResearchTypeID
AND NOT CONTAINS(re.PubNumber,
er then just
return it as-is
Hope that helps...
Mark
-Original Message-
From: Kamie Curfman [mailto:[EMAIL PROTECTED]
Sent: Friday, May 06, 2005 3:02 PM
To: CF-Talk
Subject: SQL IF/ELSE or CASE Statement Help
Hello, everyone. I posted this question on the SQL listserv, but thi
One (maybe) easy solution would be to store both parts of the pub number in
seperate colums...
Ian
- Original Message -
From: "Kamie Curfman" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Friday, May 06, 2005 8:01 PM
Subject: SQL IF/ELSE or CASE Statement He
Hello, everyone. I posted this question on the SQL listserv, but things are
pretty quiet over there. I'm hoping someone over here can help me with one of
my queries. I'm working with a SQL Server table that has "PubNumber" as one of
its varchar columns. PubNumber *typically* is a hybrid of t
--- -- --- --
1 02/01/2005 2 01JS1004
2 02/01/2005 3 01JS1003
(2 row(s) affected)
-Original Message-
From: James Holmes [mailto:[EMAIL PROTECTED]
Sent: Monday, March 14, 2005 11:43
-
From: Hua Wei [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 15 March 2005 12:14
To: CF-Talk
Subject: RE: sql select statement help..
Cfcoder,
=
select count(*) as numofjobs, jobPriority, jobNumber from jobs where
logdatetime >= #startdate# and logdatetime <= #stopda
rderby
=
-Original Message-
From: cf coder [mailto:[EMAIL PROTECTED]
Sent: Monday, March 14, 2005 9:23 AM
To: CF-Talk
Subject: sql select statement help..
Hello everybody, I really need someone to help me here. I'm developing a
graphical interface to report the no of jobs logged in a pe
Hello everybody, I really need someone to help me here. I'm developing a
graphical interface to report the no of jobs logged in a period. The x axis
displays the period (days) and the y-axis displays the number of jobs logged in
a given day.
The data is pulled from the jobs table. The job logge
Hi Erick,
I appreciate your reply. LogNumber column is a primary key so you cannot have two records with the same LogNumber. This is what I'm trying to do.
LogNumber Linked Call LogTitle
01DC Log A
01DC 01DC Log B
01DC 01DC Log C
Because
CF-Talk
Subject: sql select statement help..
Hello everybody,
I need your help with some sql select statement.
Let me expalin..
I'm working on a logging system for a support desk which allows customers to
call the support desk and log a call if something needs to be done or say
for example
Can somebody please help me!
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Hello everybody,
I need your help with some sql select statement.
Let me expalin..
I'm working on a logging system for a support desk which allows customers to call the support desk and log a call if something needs to be done or say for example some equipment needs to be brought.
So, coming to
12:40 PM
To: CF-Talk
Subject: Re: SQL Max Statement Help
Hjust off the top of my head Mark (completely untested/unverified),
does this return what you want?
SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID, max(T.paidThru)
GROUP BY M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID
- Original Message -
From: Mark Leder
To: CF-Talk
Sent: Tuesday, June 15, 2004 11:15 AM
Subject: SQL Max Statement Help
Hi All,
Two tables I'm trying to
Hi All,
Two tables I'm trying to query:
1) Membership table, each with a unique "memberID"
2) Transaction table, could be many records associated with one member ID
(one-to-many). Has a unique ID (transaction ID, along with "memberID"
column(for joining) and a "paidThru" column.
I want to query
#'
>GROUP BY UserName, SearchString
>
>
>hth
>
>...tony
>
>tony weeg
>senior web applications architect
>navtrak, inc.
>www.navtrak.net
>[EMAIL PROTECTED]
>410.548.2337
>
>-Original Message-----
>From: ColdFusion Programmer [mailto:[EMAIL PROTECTE
ColdFusion Programmer wrote:
>
> #DateFormat(Now()-7,"/mm/dd")# - #qTmp.DateTime#
>
> SELECT UserName, SearchString, Count(SearchString) AS Occurences
> FROM qTmp
> WHERE < DateTime
> GROUP BY UserName, SearchString
>
Any NULLs in qTmp? What datatype is DateTime in the database?
ssage-
From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 11:59 AM
To: CF-Talk
Subject: Re:SQL Statement help!
I've tried this and it errors:
#DateFormat(Now()-7,"/mm/dd")# - #qTmp.DateTime#
SELECT UserName, SearchString, Count(SearchString
ony
>>
>>tony weeg
>>senior web applications architect
>>navtrak, inc.
>>www.navtrak.net
>>[EMAIL PROTECTED]
>>410.548.2337
>>
>>-Original Message-
>>From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
>>Sent: Friday, October 10, 2003 9:
riday, October 10, 2003 10:31 AM
To: CF-Talk
Subject: Re:SQL Statement help!
I think this is what you mean
DateTime - 2003/09/01 11:52:16
Now - {ts '2003-10-10 15:28:19'}
>whats the value of dateTime parse out to?
>
>
>...tony
>
>tony weeg
>senior web applications archi
]
>410.548.2337
>
>-Original Message-
>From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
>Sent: Friday, October 10, 2003 9:32 AM
>To: CF-Talk
>Subject: Re:SQL Statement help!
>
>I now get a different error
>
>Query Of Queries runtime error.
>Unsupported type c
Subject: Re:SQL Statement help!
I now get a different error
Query Of Queries runtime error.
Unsupported type comparison.
>ColdFusion Programmer wrote:
>> Guys, I'm sorry for so many errors on my part. Jochem, the code your
>> posted throws an error,
>>
>> Error Execu
I now get a different error
Query Of Queries runtime error.
Unsupported type comparison.
>ColdFusion Programmer wrote:
>> Guys, I'm sorry for so many errors on my part. Jochem, the code your
>> posted throws an error,
>>
>> Error Executing Database Query.
>>
>> Query Of Queries syntax error.
>>
ColdFusion Programmer wrote:
> Guys, I'm sorry for so many errors on my part. Jochem, the code your
> posted throws an error,
>
> Error Executing Database Query.
>
> Query Of Queries syntax error.
> Encountered "{" at line 0, column 0. Incorrect conditional
> _expression_, Expected one of [like|nul
D]
410.548.2337
-Original Message-
From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 9:00 AM
To: CF-Talk
Subject: Re:SQL Statement help!
Guys, I'm sorry for so many errors on my part. Jochem, the code your posted
throws an error,
Error Executing Datab
tions architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:56 AM
To: CF-Talk
Subject: RE: SQL Statement help!
Tony Weeg wrote:
> I guess standards matter in some ca
Guys, I'm sorry for so many errors on my part. Jochem, the code your posted throws an error,
Error Executing Database Query.
Query Of Queries syntax error.
Encountered "{" at line 0, column 0. Incorrect conditional _expression_, Expected one of [like|null|between|in|comparison] condition,
A
Tony Weeg wrote:
> I guess standards matter in some cases, but, jochem, this is fairly
> clear in its descriptive text of a function available to sql server
> programmers.
>
> --from transact sql help docs-
Transact SQL <> SQL
Why do you think I explicitly refered Alla
Tony Weeg wrote:
> and besides that, what are the values that you are expecting
> to do a subtraction operation with?
>
> to me, I don't see how, (#Now()# - DateTime) would make sense
> regardless.
In full featured databases, they return an interval. Unfortunately,
neither Java nor JDBC has an in
ColdFusion Programmer wrote:
> This is what I'm doing, and I get an error. I'm using SQL Server
> 2000
No, you are not. Since your dbtype is "query", you are using CF.
>
> SELECT UserName, SearchString, Count(SearchString) AS Occurences
> FROM
> WHERE (#DateFormat(Now(),"/mm/dd")#
> #TimeFor
Dieten [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:39 AM
To: CF-Talk
Subject: RE: SQL Statement help!
Tony Weeg wrote:
> now() isnt a sql function, getDate() is tho' :)
Neither are SQL functions (copies of ISO 9075-2:1999 are available through
your national standards orga
getDate isnt?
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:39 AM
To: CF-Talk
Subject: RE: SQL Statement help!
Tony
Tony Weeg wrote:
> now() isnt a sql function, getDate() is tho' :)
Neither are SQL functions (copies of ISO 9075-2:1999 are available
through your national standards organisation).
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
ssage-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:34 AM
To: CF-Talk
Subject: RE: SQL Statement help!
now() isnt a sql function, getDate() is tho' :)
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548
-Talk
Subject: Re:SQL Statement help!
Sorry this is the query I'm using
SELECT UserName, SearchString, Count(SearchString) AS Occurences FROM qTmp
WHERE (#Now()# - DateTime) < 7 GROUP BY UserName, SearchString
>This is what I'm doing, and I get an error. I'm using SQL Serve
Sorry this is the query I'm using
SELECT UserName, SearchString, Count(SearchString) AS Occurences
FROM qTmp
WHERE (#Now()# - DateTime) < 7
GROUP BY UserName, SearchString
>This is what I'm doing, and I get an error. I'm using SQL Server 2000
>
>
>SELECT UserName, SearchString, Count(SearchStr
ace, yourTableName, with, wellyour table name.
hth
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-Original Message-
From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:24 AM
To:
This is what I'm doing, and I get an error. I'm using SQL Server 2000
SELECT UserName, SearchString, Count(SearchString) AS Occurences
FROM
WHERE (#DateFormat(Now(),"/mm/dd")# #TimeFormat(Now(),"HH:mm:ss")# - #qTmp.DateTime#) < 7
qTmp
GROUP BY UserName, SearchString
I get this error:
Quer
ColdFusion Programmer wrote:
> Hi Jochem, Can you explain what the where statement in your code
> does, I don't understand the the "EXTRACT(DAYS FROM
> CURRENT_TIMESTAMP - DateTime) < 7" bit
Read it inside out:
CURRENT_TIMESTAMP -> the current date and time, 'now'
DateTime -> your fiel
Hi Jochem, Can you explain what the where statement in your code does, I don't understand the the "EXTRACT(DAYS FROM CURRENT_TIMESTAMP - DateTime) < 7" bit
Many Thanks
>Allan Clarke wrote:
>>
>> I have this table that keps a log
>> of the user search activity. For ex, on the site
>> search page of
Allan Clarke wrote:
>
> I have this table that keps a log
> of the user search activity. For ex, on the site
> search page of the site, if a user searches for
> a keyword "test", it gets stored on the database table
> along with other details like the
> username, date/time, search category and the
I was wondering if the SQL gurus out there could help.
I have this table that keps a log
of the user search activity. For ex, on the site
search page of the site, if a user searches for
a keyword "test", it gets stored on the database table
along with other details like the
username, date/time, sea
Not too sure of format off the top of my head, but you it looks like you
need something like
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, April 23, 2001 1:45 PM
Subject: Re: statement help...
> I know there is more than one because i am looking in that specific table
>
m: "Jay Patton" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Monday, April 23, 2001 12:16 PM
> Subject: Re: statement help...
>
>
> > i tried this also, and it still didnt work
> > since then i have switched every
: Monday, April 23, 2001 12:17 PM
To: CF-Talk
Subject: Re: statement help...
i tried this also, and it still didnt work
since then i have switched everything to be id specific now however, it
still only returns the first record w/ that id, now. any ideas this is
my query now
SELECT *
FROM
PROTECTED]]
Sent: 23 April 2001 18:07
To: CF-Talk
Subject: Re: statement help...
How do you know there are 12-ish if you only see 1??
Are you saying that getRodeos.RecordCount = 1, or have you maybe forgotten
to specify queryname in cfoutput loop?
Dave
- Original Message -
From
; <[EMAIL PROTECTED]>
Sent: Monday, April 23, 2001 12:16 PM
Subject: Re: statement help...
> i tried this also, and it still didnt work
> since then i have switched everything to be id specific now however, it
> still only returns the first record w/ that id,
lto:[EMAIL PROTECTED]]
Sent: Monday, April 23, 2001 12:17 PM
To: CF-Talk
Subject: Re: statement help...
i tried this also, and it still didnt work
since then i have switched everything to be id specific now however, it
still only returns the first record w/ that id, now. any ideas this is
my qu
ril 23, 2001 9:48 AM
Subject: Re: statement help...
> Try the following as your where clause:
>
> WHERE trim(Circuit) = '#trim(url.CircuitName)#'
>
> *** REPLY SEPARATOR ***
> On 4/23/2001 at 9:31 AM Jay Patton wrote:
>
> |Why would this only select one
Try the following as your where clause:
WHERE trim(Circuit) = '#trim(url.CircuitName)#'
*** REPLY SEPARATOR ***
On 4/23/2001 at 9:31 AM Jay Patton wrote:
|Why would this only select one record from the db?(there are about 12 it
|should return but it only returns one (which is t
Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 23, 2001 11:32 AM
To: CF-Talk
Subject: statement help...
Why would this only select one record from the db?(there are about 12 it
should return but it only returns one (which is the first one)
SELECT *
FROM RodeoM
Why would this only select one record from the db?(there are about 12 it should return
but it only returns one (which is the first one)
SELECT *
FROM RodeoManager
WHERE Circuit = '#url.CircuitName#'
Thanks,
Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1
uld be
better.
Let me know if none of the above helps and I can work on a query using a
virtual table for you.
Bob
-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 5:11 PM
To: CF-Talk
Subject: Re: Statement Help!!
would it be wise to try a sub query wit
-
From: "Bob Silverberg" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 1:47 PM
Subject: RE: Statement Help!!
> What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?
>
> Bob
>
> -Original
quot;CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 1:47 PM
Subject: RE: Statement Help!!
> What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?
>
> Bob
>
> -Original Message-
> From: Jay Patton [mailto:[EMAIL PROTECTED]]
>
What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?
Bob
-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 3:09 PM
To: CF-Talk
Subject: Statement Help!!
what i am trying to accomplish here is to show ONLY those contestants that
Subject: Statement Help!!
what i am trying to accomplish here is to show ONLY those contestants that
have 2 or more DIFFERANT EventID's in the ResultsManager (table in my SQL7
DB) (example: if they do the same event in every rodeo for the year they
should NOT show up, but if they do 2 or more
what i am trying to accomplish here is to show ONLY those contestants that have 2 or
more DIFFERANT EventID's in the ResultsManager (table in my SQL7 DB) (example: if they
do the same event in every rodeo for the year they should NOT show up, but if they do
2 or more they should) the following
58 matches
Mail list logo