Re: SQL Statement help!
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? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Statement help!
first of all, whats this?
#DateFormat(Now()-7,"/mm/dd")# - #qTmp.DateTime#
dateAdd() needs to be in there for the -7 thing to work.
here is the below in correct format.
SELECT UserName, SearchString, Count(SearchString) AS Occurences
FROM qTmp
WHERE '#dateFormat(dateAdd('d',Now(),-7),'mm/dd/')#' <
'#dateFormat(dateTime,'mm/dd/')#'
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 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) AS Occurences
FROM qTmp
WHERE
value="#DateFormat(Now()-7,'/mm/dd')#"> < DateTime
GROUP BY UserName, SearchString
>ok so format that value, with dateFormat, enclose it in 's and you
>should be good.
>
>...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 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 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:32 AM
>>To: CF-Talk
>>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 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,
>>>
>>>SELECT UserName,
>>> SearchString,
>>> Count(SearchString) AS Occurences
>>>FROM qTmp
>>>WHERE
>>>value="#DateAdd("d",-7,Now())#"> < DateTime GROUP BY UserName,
>>> SearchString
>>>
>>>Jochem
>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: SQL Statement help!
ok so format that value, with dateFormat, enclose it in 's and you should be
good.
...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 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 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:32 AM
>To: CF-Talk
>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 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,
>>
>>SELECT UserName,
>> SearchString,
>> Count(SearchString) AS Occurences
>>FROM qTmp
>>WHERE
>>value="#DateAdd("d",-7,Now())#"> < DateTime GROUP BY UserName,
>> SearchString
>>
>>Jochem
>>
>>
>>
>>
>
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: SQL Statement help!
whats the value of dateTime parse out to?
...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 9:32 AM
To: CF-Talk
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 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,
>
>SELECT UserName,
> SearchString,
> Count(SearchString) AS Occurences
>FROM qTmp
>WHERE
>value="#DateAdd("d",-7,Now())#"> < DateTime GROUP BY UserName,
> SearchString
>
>Jochem
>
>
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: SQL Statement help!
its because of the odbc datetime format that that will give you...the
getDate function that is...and the qOfq prolly doesn't like that? try,
adding a dateFormat() around that, so that its in a format the qOfq will
jive with.
SELECT UserName,
SearchString,
Count(SearchString) AS Occurences
FROM qTmp
WHERE '#dateFormat(DateAdd("d",-7,Now(),'mm/dd/'))#' < DateTime
GROUP BY UserName, SearchString
or something like that.
...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 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 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,
Any ideas?
>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")# #TimeFormat(Now(),"HH:mm:ss")# -
>> #qTmp.DateTime#) < 7 qTmp GROUP BY UserName, SearchString
>
>SELECT UserName,
> SearchString,
> Count(SearchString) AS Occurences
>FROM qTmp
>WHERE #DateAdd("d",-7,Now())# < DateTime
>GROUP BY UserName, SearchString
>
>Jochem
>
>
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: SQL Statement help!
your 100% right. but his arena is sql server. so in his world, transactsql == sql, at least for today, and at least for this answer he is looking for. whatever, have a good morning...i don't have the mental time to devote to this argument :) later. ...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:56 AM To: CF-Talk Subject: RE: SQL Statement help! 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 Allan to the manual of his database? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Statement help!
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 Allan to the manual of his database? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Statement help!
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 interval datatype, so they will most likely be converted to a string such as "1 month 3 days 15:53:12". Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Statement help!
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- GETDATE Returns the current system date and time in the MicrosoftR SQL ServerT standard internal format for datetime values. Syntax GETDATE ( ) Return Types datetime Remarks Date functions can be used in the SELECT statement select list or in the WHERE clause of a query. In designing a report, GETDATE can be used to print the current date and time every time the report is produced. GETDATE is also useful for tracking activity, such as logging the time a transaction occurred on an account. Examples A. Use GET DATE to return the current date and time This example finds the current system date and time. SELECT GETDATE() GO Here is the result set: - July 29 1998 2:50 PM (1 row(s) affected) B. Use GETDATE with CREATE TABLE This example creates the employees table and uses GETDATE for a default value for the employee hire date. USE pubs GO CREATE TABLE employees ( emp_id char(11) NOT NULL, emp_lname varchar(40) NOT NULL, emp_fname varchar(20) NOT NULL, emp_hire_date datetime DEFAULT GETDATE(), emp_mgr varchar(30) ) GO --from transact sql help docs- ...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 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]
RE: SQL Statement help!
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 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]
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 organisation). Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Statement help!
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. are you trying to see if the number of days between getDate() (or now() as you have it) are greater than seven? sql server has a dateAdd() function similar to cfmx. I don't remember the exact syntax for it off hand...sql server query analyzer has a great help file tho' ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- 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.2337 -Original Message- From: ColdFusion Programmer [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 8:35 AM To: CF-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 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: > >Query Of Queries syntax error. >Encountered "WHERE" at line 0, column 0. > >>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 field with the moment of the search, 'then' >>CURRENT_TIMESTAMP - DateTime -> the interval between now and >then >>EXTRACT(DAYS FROM ..) -> the number of whole days in that >interval >> >>So what it says is that DateTime should be less than 7 days in the >>past in standard SQL syntax. If your database does not support >>standard SQL syntax, you might have to rewrite it in something your >>database understands, but that should be explained in the manual. >> >>Jochem >> >> >> > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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.2337 -Original Message- From: ColdFusion Programmer [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 8:35 AM To: CF-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 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: > >Query Of Queries syntax error. >Encountered "WHERE" at line 0, column 0. > >>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 field with the moment of the search, 'then' >>CURRENT_TIMESTAMP - DateTime -> the interval between now and >then >>EXTRACT(DAYS FROM ..) -> the number of whole days in that >interval >> >>So what it says is that DateTime should be less than 7 days in the >>past in standard SQL syntax. If your database does not support >>standard SQL syntax, you might have to rewrite it in something your >>database understands, but that should be explained in the manual. >> >>Jochem >> >> >> > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Statement help!
"from where" doesn't jive. SELECT UserName, SearchString, Count(SearchString) AS Occurences FROM yourTableName WHERE (#DateFormat(Now(),"/mm/dd")# #TimeFormat(Now(),"HH:mm:ss")# - #qTmp.DateTime#) < 7 qTmp GROUP BY UserName, SearchString replace, 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: CF-Talk Subject: Re:SQL Statement help! 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: Query Of Queries syntax error. Encountered "WHERE" at line 0, column 0. >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 field with the moment of the search, 'then' >CURRENT_TIMESTAMP - DateTime -> the interval between now and then >EXTRACT(DAYS FROM ..) -> the number of whole days in that interval > >So what it says is that DateTime should be less than 7 days in the past >in standard SQL syntax. If your database does not support standard SQL >syntax, you might have to rewrite it in something your database >understands, but that should be explained in the manual. > >Jochem > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Statement help!
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 results > found. > The columns are > DateTime, UserName, SearchKeyword, SearchCategory and > ResultsFound > I want to display it like this > In the last 7 days > powerpoint,word,excel,notepadtestUser1 SELECT UserName, SearchKeyWord, Count(SearchKeyWord) AS Occurences FROM table WHERE EXTRACT(DAYS FROM CURRENT_TIMESTAMP - DateTime) < 7 GROUP BY UserName, SearchKeyWord Then use cfoutput with the group attribute to display everything n one line. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SQL Statement help!
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, search category and the results found. The columns are DateTime, UserName, SearchKeyword, SearchCategory and ResultsFound I am displaying this information in a report to the admin user. It currently displays all the information using a simple select statement. I want to display all search keywords a user has searched for in one row. So instead of displaying the information like this 09/09/2003 17:41:26 powerpoint testUser1 09/09/2003 18:42:26 word testUser1 09/09/2003 19:44:26 excel testUser1 09/09/2003 20:45:26 notepad testUser1 I want to display it like this In the last 7 days powerpoint,word,excel,notepadtestUser1 Can somebody show me how to do this please Best Regards Allan __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
