cant you just use Date functions....
Day(GetDate())
Month(Getdate())
Year(GetDate()) .. these day functions are available in sql server...
i followed this thread.. just took a quick look at the SQL
> 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)
>
----- Original Message -----
From: "Chris" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, September 11, 2002 2:27 PM
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
>
>
>
>
>
>
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
