tablenames or aliases....
SELECT COUNT(*) as count_ttl,
DATEPART(dy, cl1.logdate) as doy,
DATEPART(yyyy, cl1.logdate) as yr,
DATEPART(mm, cl1.logdate) as mnth,
DATEPART(d, cl1.logdate) as dy,
count(cl1.logdate) as count_in
FROM customer_log as cl2, customer_log as cl1
WHERE logdate BETWEEN #start# AND #DateAdd('D', 1, end)#
and cl1.logdate = cl2.logdate
and cl1.logtype <> 'callout'
GROUP BY DATEPART(dy, logdate), DATEPART(yyyy, logdate),
DATEPART(mm, logdate), DATEPART(d, logdate)
ORDER BY DATEPART(dy, logdate)
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
TeleCommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Eric Hoffman [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 11, 2002 11:57 AM
To: CF-Talk
Subject: RE: Man this query @#%$&*
You need to secify the tablenames in the dateart I am sure.
Regards,
Eric J Hoffman
DataStream Connexion
www.datastreamconnexion.com
Delivering Creative Data Solutions
-----Original Message-----
From: Chris [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 11, 2002 1:27 PM
To: CF-Talk
Subject: RE: Man this query @#%$&*
I get an "Ambiguous column name 'logdate'." When I use this.
-----Original Message-----
From: Dan Haley [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 11, 2002 12:56 PM
To: CF-Talk
Subject: RE: Man this query @#%$&*
Can you just do a join between the two?
SELECT COUNT(*) as count_ttl,
DATEPART(dy, logdate) as doy,
DATEPART(yyyy, logdate) as yr,
DATEPART(mm, logdate) as mnth,
DATEPART(d, logdate) as dy,
count(cl1.logdate) as count_in
FROM customer_log as cl2, customer_log as cl1
WHERE logdate BETWEEN #start# AND #DateAdd('D', 1, end)#
and cl1.logdate = cl2.logdate
and cl1.logtype <> 'callout'
GROUP BY DATEPART(dy, logdate), DATEPART(yyyy, logdate),
DATEPART(mm, logdate), DATEPART(d, logdate)
ORDER BY DATEPART(dy, logdate)
Dan
-----Original Message-----
From: Chris [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 11, 2002 10:42 AM
To: CF-Talk
Subject: Man this query @#%$&*
Can anyone out there assist me with this query? I need to count two
sets, differentiated by a text field and grouped by yet another field.
Here's what I have:
SELECT COUNT(*) as count_ttl,
DATEPART(dy, logdate) as doy,
DATEPART(yyyy, logdate) as yr,
DATEPART(mm, logdate) as mnth,
DATEPART(d, logdate) as dy,
(SELECT COUNT(*)
FROM customer_log as cl1
WHERE logtype <> 'callout'
AND cl1.logdate = cl2.logdate) AS count_in
FROM customer_log as cl2
WHERE logdate BETWEEN #start# AND #DateAdd('D', 1, end)#
GROUP BY DATEPART(dy, logdate), DATEPART(yyyy, logdate),
DATEPART(mm, logdate), DATEPART(d, logdate)
ORDER BY DATEPART(dy, logdate)
The problem is that cl2.logdate is not used in the GROUP BY clause nor
can I use it. Logdate contains a time stamp which screws up the GROUP
BY day functionality which is necessary for my report, thus the
DATEPART. Basically, I'm counting all lines in a date range but also
need to count just the lines in which the logtype is a specified value.
HELP!
cc
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
