Date query for months

[olmos]

I am trying to loop through all the months so that I can get a count of events
that occur for each month based on notification date. It is looping through, but I am not getting any records back. What am I doing wrong? I tried using LIKE instead of = in the WHERE clause but that did not work. I am using Oracle database.

Thanks in advance for your help,
olmos

<CFSET months = ArrayNew(1)>
<CFLOOP INDEX = &quot;loopcount&quot; FROM=&quot;1&quot; TO=&quot;12&quot;>
<CFSET months[loopcount] = MonthasString(loopcount)>
</CFLOOP>

<CFLOOP index = &quot;loopcount&quot; FROM=&quot;1&quot; TO=&quot;12&quot;>

<CFOUTPUT>

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

SELECT count(id) as id_count
FROM event
WHERE notification_date = TO_DATE('#ucase(months[loopcount])# 2002', 'MONTH YYYY')

</cfquery>

#events_by_month2.id_count#
#months[loopcount]#<br>

</CFOUTPUT>

</CFLOOP>

 

[RedLion]

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

SELECT count(month(notification_date)) as counts, month(notification_date) as eachMonth
FROM event
GROUP BY month(notification_date)
order by eachMonth
</cfquery>

<cfoutput query=&quot;events_by_month&quot;>
There are #counts# events in #eachMonth#.<br>
</cfoutput>


Charl

 

[olmos]

Charl, Thanks for your help.
I kept getting invalid column and the order was in alphabetical order and not by sorted by date, so I tried it this way:

<CFQUERY NAME=&quot;events_by_month&quot; DATASOURCE=&quot;#PrimaryDataSource#&quot; >
SELECT count(to_char(notification_date, 'YY MM')) as &quot;counts&quot;,
to_char(notification_date, 'YY MM') as &quot;eachMonth&quot;
FROM event
GROUP BY to_char(notification_date, 'YY MM')
ORDER BY &quot;eachMonth&quot;
</cfquery>

So now it sorts by date but , I still need to show the full name or 3 letter name for the month.

This is what I tried :

<cfoutput query=&quot;events_by_month&quot;>
<CFSET eachMonth = #DateFormat(eachMonth,&quot;MMMM YY&quot#>

There are #counts# events in #eachMonth#<br>

</cfoutput>

But it didn't work, any suggestions?

** My 2nd problem is - I need to get a category count for each event, again which
will be for each month. Here is what I have so far:

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

SELECT count(c.eventcategory_id) as &quot;category&quot;, to_char(e.notification_date, 'YY MM') as &quot;eachMonth&quot;

FROM event e , computer c

WHERE e.event_id = c.event_id

AND e.event_name like c.system_name

GROUP BY c.incidentcategory_id

ORDER BY &quot;eachMonth&quot;

</cfquery>

The Error I get is : ORA-00979: not a GROUP BY expression
I am not sure how to construct this query. I have to get a category count when it the e.event_name is like c.system_name.

Thanks in advance for your help,
olmos

 

[RedLion]

Hi again,
Problem 1:
Make a table months with monthnr and monthname place all month numbers with the description for it that will suit you most.

<cfoutput query=&quot;qMonthDescription&quot;>
<CFQUERY NAME=&quot;events_by_month&quot; DATASOURCE=&quot;#PrimaryDataSource#&quot; >
SELECT monthname
FROM months
where monthnr = #eachMonth#
</cfquery>

There are #qMonthDescription.counts# events in #qMonthDescription.monthname#<br>

</cfoutput>


I hope this will help, sorry but I have to go now, but I'll come back and help you another day if no one else has done it, nice weekend all of you,

Charl

 

[RedLion]

2. I hope problem 1 is solved by this time. And I guess you haven't solved problem 2 already, so I will give it a try.

I'm not sure if the aliases in the select clause may be referenced in the selection, so I suggest to use the whole expression. And when you would like to get the number of events in one month you should group by months, category and count the categories as you've already done.

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

SELECT count(c.eventcategory_id) as category, to_char(e.notification_date, 'YY MM') as eachMonth

FROM event e , computer c

WHERE (e.event_id = c.event_id)

AND (e.event_name like c.system_name)

GROUP BY to_char(e.notification_date, 'YY MM'), c.incidentcategory_id

ORDER BY to_char(e.notification_date, 'YY MM')
</cfquery>


I hope it helps, let me know,

Charl