select count(createdate), createdate
from (select convert(varchar, creationdate, 101) AS createdate
from referer) makedate
group by createdate
______________________________________________________
<file:///E:/EUColor.gif>
Bill Grover
Manager, Information Systems Phone: 301.424.3300 x3324
EU Services, Inc. FAX: 301.424.3696
649 North Horners Lane E-Mail: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
Rockville, MD 20850-1299 WWW: <http://www.euservices.com/> http://www.euservices.com
______________________________________________________
-----Original Message-----
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]
Sent: Sunday, March 14, 2004 2:39 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
That's a neat trick - saves a lot of typing. Thanks!
-----Original Message-----
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 2:35 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
thank you!
-----Original Message-----
From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 3:31 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
The numbers in the 3rd position of the convert are qualifiers for how it
will convert. In this case 101 = USA = MM/DD/YY.
If you look in the SQL Books Online under Cast and Convert it will give
you
a list of all of the date conversions.
-----Original Message-----
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 3:18 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
WOW! WOW! WOW! Thanks so much. I knew this was possible.
what is the significanse of the 101
-----Original Message-----
From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 3:03 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
Che,
If you are on MSSql 2K then this is the solution:
select count(convert(varchar, creationdate, 101)), convert(varchar,
creationdate, 101)
from referer
group by convert(varchar, creationdate, 101)
Steve
-----Original Message-----
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 2:52 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
mark, i see where you are coming from...alas, i still am having no luck.
-----Original Message-----
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 2:51 PM
To: CF-Talk
Subject: RE: Quick SQL Question...
The 2 are mutually exclusive. One is the "group" of all the stuff on
this
day, but the other (the actual date) is not merely a date - but a - by
nature a very unique item - down to the second. So you are actually
getting
the grouping you are asking for. If 2 referals occured at the exact
instant
(say '3/4/2004 5:07:35 PM') ... then you would see "2 3/4/2004 5:07:35
PM" -
but since that's unlikey this is more or less like doing a "select *
from.....". What you need is a label and an order by clause:
SELECT COUNT(creationdate) AS TotalRefsPerDay,
datepart(month,creationdate)
+ '-' + datepart(day,creationdate) + '-' + datepart(year,creationdate)
AS
datelabel
group by datepart(dw,creationdate)
order by datelabel
or something similar. The DateName( ) function would work as well -
maybe
better. It usally takes some work to get it exactly right.
-Mark
Mark A. Kruger, MCSE, CFG
www.cfwebtools.com
www.necfug.com
http://blog.mxconsulting.com
...what the web can be!
-----Original Message-----
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Friday, March 12, 2004 1:28 PM
To: CF-Talk
Subject: Quick SQL Question...
This SQL Code works:
SELECT COUNT(creationdate) AS TotalRefsPerDay
FROM Referer
GROUP BY DATEPART(dw, creationdate)
197
364
350
294
260
243
196
But when I add a column... this does not):
SELECT COUNT(creationdate) AS TotalRefsPerDay, creationdate
FROM Referer
GROUP BY DATEPART(dw, creationdate), creationdate
1 3/4/2004 5:07:35 PM
1 3/4/2004 5:09:27 PM
1 3/4/2004 5:11:19 PM
1 3/4/2004 5:13:14 PM
1 3/4/2004 5:14:31 PM
1 3/4/2004 5:14:53 PM
1 3/4/2004 5:16:19 PM
1 3/4/2004 5:20:37 PM
1 3/4/2004 5:21:10 PM
What am I doing wrong? I'd like a simple two column table: creation
date
and the total number of referers for each day!
TIA...Che
_____
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
