- see footer for list info -<
yep that's what I meant ;)
Adrian Lynch wrote:
- see footer for list info -<
Well if that works, try:
<CFQUERY NAME="GetRequests2" DATASOURCE="#request.maindsn#">
SELECT day(R.RequestDate) as rd, Count(R.RequestID) AS Total
FROM Requests R
WHERE R.RequestDate BETWEEN #attributes.fromdate# AND
#attributes.todate#
GROUP BY MONTH(R.RequestDate), day(R.RequestDate)
</CFQUERY>
or swap the group by around. You might want YEAR() in there as well.
Ade
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Allan Cliff
Sent: 14 June 2005 14:01
To: CF Developer
Subject: [CF-Dev] Grouping by date in SQL
- see footer for list info -<
I want to group a table by date.
The date column is a date/time column but I want to group on a per day
basis.
i.e.
<CFQUERY NAME="GetRequests2" DATASOURCE="#request.maindsn#">
SELECT day(R.RequestDate) as rd, Count(R.RequestID) AS Total
FROM Requests R
WHERE R.RequestDate BETWEEN #attributes.fromdate# AND
#attributes.todate#
GROUP BY day(R.RequestDate)
</CFQUERY>
This works fine but when the date span is over a month then the 1st June
is obvioulsy treated as 1 as is the first of any month
I want to group by the date irrespective of the time.
Hope you get it.
Allan
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.1 - Release Date: 13/06/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.1 - Release Date: 13/06/2005
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
