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 ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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