Loop by Month

[ndeeley]

Hi,

I have some data I need to total by Month.

i can create a query to do this for a particular month:

 <cfset vStart=createDate(year(Now()), 3, 1)>
<cfset vEnd=createDate(year(Now()), 3, 31)>
 
<cfquery name="getSum">
select sum(TimeTaken) as mySum, CompletionDate
from tblTaskBank
where CompletionDate BETWEEN #vStart# AND #vEnd#
group by TimeTaken, CompletionDate
</cfquery>
 

Which is great for one month, but I need to calculate the sum for each of the twelve months in a financial year (April - March).

I can loop through the dates to create start and end values:

<cfloop index="i" from="1" to="12">
    <cfset vStartM = dateFormat(dateAdd("m", i, vStart), 'dd/mm/yyyy')>
    <cfset vEndM = dateFormat(dateAdd("m", i, vEnd), 'dd/mm/yyyy')>
</cfloop>
 
 

but how do i store these date values, then apply them to the query, and loop through the query so I get the sum for each month?

It's been doing my head in! And advice would be appreciated!

Thanks
Neil

[Rabbit]

I would just use

SELECT Month(DateField), Sum(ValueField) AS SumOfValueField
FROM tableName
GROUP BY Month(DateField)

[ndeeley]

hi Rabbit,

That's great thanks. Just one thing - the month value is returned as a number, which despite formatting with DateFormat doesn't display the correct month name. Do I need to use CreateODBCDate?

Thanks

[Rabbit]

You want to display the text value of the month? You do one of two things. One, in your code, use a case statement to convert the month number to text (unless such a function already exists in coldfusion, I've never used it). Or, in the SQL, get the month number, append '/01/2011' and convert that to a date and then get the month name.