looping over a query

[olmos]

I am having trouble with looping over a query in order to get totals for events in different categories and also per month. The first CFLOOP Query works for the year, but the next one which is the problem is not working, How can I get each category total to output per month, I am not getting the results.
I would really appreciate any suggestions to get this to work.

Thanks,
olmos

<cfoutput>
<CFQUERY NAME=&quot;categories&quot; DATASOURCE=&quot;#PrimaryDataSource#&quot; >
SELECT eventcategory_id
FROM eventcategory
</cfquery>

<CFQUERY NAME=&quot;events&quot; DATASOURCE=&quot;#PrimaryDataSource#&quot; >

SELECT distinct to_char(e.notification_date, 'MON-YY') month_year , c.eventcategory_id, c.event_id

FROM computer c, event e

Where c.event_id = e.event_id

AND e.notification_date BETWEEN '#fiscal_year_start#' AND '#fiscal_year_end#'

ORDER BY month_year
</cfquery>

***This is ok I do get the totals for each category for the year***

<cfloop query = &quot;categories&quot;>

<CFQUERY NAME=&quot;get_event_by_cat&quot; DATASOURCE=&quot;#PrimaryDataSource#&quot; >

SELECT count(c.event_id) &quot;total&quot;

FROM computer c, event e

Where c.event_id = e.event_id

AND e.notification_date BETWEEN '#fiscal_year_start#' AND '#fiscal_year_end#'

AND c.eventcategory_id = '#eventcategory_id#'

</cfquery>

<cfset event_id = ''>
<cfloop query=&quot;get_event_by_cat&quot;>
<cfset event_id = ListAppend(event_id, total)>

<tr>

<td> #total#</td>

</tr>


</cfloop>

</cfloop>

</cfoutput>

*** Here is the problem , I am not getting the totals for each month,
and it is not looping through each category, What am I doing wrong ?,
How can I get each category total to output per month, I just get DEC-01
***

<cfoutput>


<cfloop query = &quot;events&quot;>

<CFQUERY NAME=&quot;get_event_by_month&quot; DATASOURCE=&quot;#PrimaryDataSource#&quot; >

SELECT count(to_char(e.notification_date, 'MON-YY')) as &quot;totalmonth&quot;

FROM computer c, event e

Where c.event_id en (#event_list#)

AND c.event_id = e.event_id

AND c.eventcategory_id = '#eventcategory_id#'

AND e.notification_date = to_date('#events.month_year#', 'MON-YY')


</cfquery>


<cfset month = ''>
<cfloop query=&quot;get_event_by_month&quot;>
<cfset month = ListAppend(month, totalmonth)>



<tr>
<td>total for #events.month_year# #totalmonth#</td>
</tr>

<tr><td></td></tr>
</cfloop>

</cfloop>


</cfoutput>

 

[Guest_imported]

Rather than analyze your code to determine what you are trying to do, then reverse-engineer the broken code to determine the table structure and finnaly procude the answer that you need, it would be a lot easier if you would tell me what the table structure is and explain which fields you are using, what type of data is in them,and which fields you want counted and subtotaled.

 

[olmos]

1. This is the table structure-

Events
by Month | Category 1 | Category 2 ...
---------------------------------------------
(count # of events in cat)
Oct-01 | 7 | 3
-------------------------------------------
Nov-01 | 4 | 1
--------------------------------------------
Dec-01 | 3 | 1
--------------------------------------------
Jan-02 | 2 | 2

and so on ...

--------------------------------------------
FY total | 16 | 7
(for each cat)
--------------------------------------------

total Qtr 1 14 | 5
(Oct, Nov&Dec)

total Qtr 2 ...
(Jan, Feb, Mar)


2. fields being used - event_id (numeric), eventcategory_id (numeric), notification_date (date)


3. I need to count the number of events in each category.
Each event is assigned to a event category -> eventcategory_id and has a notification_date both are in the computer table.
eventcategory_id is the field I want counted and totaled to find out
how many times it has occured per month using notification month and year for an event.

I hope this is clearer. Please let me know if you need more explanation.
I really appreciate any help.

Thanks,
olmos.