The query needs to be supported by the DB. Here is a query I use to filter
Month and Year from a system log on MS-SQL.
Supported is DATEPART. Full month name is returned so I use the LEFT for 3
chrs.
ORDER returns grouping for MONTH/YEAR
-alan
<cfquery name="faillist" datasource="#tcdb#">
select A.JOB_ID, B.SRCSYS, B.DESTSYS, LEFT(DATENAME(mm, A.LOGDATE), 3)
AS MNAME,
DATEPART(yy,A.LOGDATE) as LOG_YEAR, COUNT(A.JOB_ID) AS RCNT,
SUM(A.JOB_FAIL) AS RFAIL
FROM TC_LOG_COUNTER A INNER JOIN
TC_JOB_INPUT B ON A.JOB_ID = B.JOB_ID
where
datepart(yy,A.LOGDATE) = #logyear#
group by A.JOB_ID, B.SRCSYS, B.DESTSYS, LEFT(DATENAME(mm, A.LOGDATE), 3),
DATEPART(yy,A.LOGDATE)
order by A.JOB_ID
</cfquery>
----- Original Message -----
From: Graham Faulkner <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 10, 2002 7:09 PM
Subject: [CFTALKTor] grouping birthdates by month
> Hi there,
>
> I'm working on a page that lists upcoming birthdays (full date, including
> year), to be listed by month, for a group of people. The birthdays
> obviously have different years associated with them. How can I group them
> under month headings in the <cfoutput>, without duplicating months?
>
> I tried the following:
>
> <cfquery name="getNames" datasource="dsn">
> select
> ID,
> LastName,
> FirstName,
> Format(BirthDate, 'mmmm') as Month,
> BirthDate,
> CountryID,
> MailingAddress
> from names
> order by BirthDate asc
> </cfquery>
>
> and then used the following <cfoutput> information:
>
> <table cellpadding="2">
> <cfoutput query="getNames" group="Month">
> <tr bgcolor="##4263bd">
> <td colspan="3"><font face="Arial, Helvetica, sans-serif" size="2"
> color="##ffffff"><b><i>#Month#</i></b></font>
> </td>
> </tr>
> <cfoutput>
> <tr bgcolor=###IIF(getNames.currentrow MOD 2, DE ('ffffff'), DE
> ('cccccc'))#>
> <td width="10%"><font face="Arial, Helvetica, sans-serif"
> size="2">#DateFormat(BirthDate,
> "mmm dd, yyyy")#</font></td>
> <td width="50%"><font face="Arial, Helvetica, sans-serif"
> size="2"><b>#FirstName# #LastName#</b></font></td>
> <td width="40%"><font face="Arial, Helvetica, sans-serif"
> size="2">#MailingAddress#</font></td>
> </tr>
> </cfoutput>
> </cfoutput>
> </table>
>
> However, the birthdays for say, May, are not all grouped together. There
is
> a separate group for May 1973, May 1963, etc.
>
> What am I missing?
>
> Blessings,
>
> Graham
>
> ------------------------------------------------------------
> Graham Faulkner, BBA
> President
> Global Audience Communications
>
> Web design with YOUR world in mind!
>
> Web: http://www.GlobalAudienceCommunications.com
> Email: [EMAIL PROTECTED]
>
> Tel./Fax: 519-880-9184
> Cell: 519-496-0887
>
> -
> You are subscribed to the CFUGToronto CFTALK ListSRV.
> This message has been posted by: "Graham Faulkner"
<[EMAIL PROTECTED]>
> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
> Manager: Kevin Towes ([EMAIL PROTECTED])
http://www.CFUGToronto.org/
> This System has been donated by Infopreneur, Inc.
> (http://www.infopreneur.net)
>
>
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "Alan Goldberg" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
