In my form, I have multiple pulldowns for the user to select.
Two are required and they are the month and the year. The others
are optional. When they submit, here is the query I use in my
action page :
select group_number,group_name,
sum(case x_flag when 'Y' then 1 else 0 end) as
yes_count_total,
sum(case x_flag when 'N' then 1 else 0 end) as No_count_total
from table1 left join table2
on table1.group_number = table2.group_number
where substring(convert(varchar,group_date,101), 1, 2) =
'#form.selectmonth#'
and substring(convert(varchar,group_date,101), 7, 4) =
'#form.selectyear#'
<cfif form.select1 is not 0>
and site = '#form.select1#'
</cfif>
<cfif form.select2i not 0>
and bnumber = '#form.select2#'
</cfif>
group by group_number,group_name
This gives me the data/output that I want.
What I need to do now is to produce a rolling 12 month
display, based on the current month and year selected. Currently,
if the month and year selected is March 2008, then that is all I
show, the yes_count_total and the no_count_total for that period.
What I need to show now is not only data for March 2008, but the
yes_count_total and the no_count_total for April 2008, May 2008,
etc, , all the way to March 2009. Of course the rolling 12 month
period will change based on the month and year selection.
What is the proper way/technique to do this ?
1
Reply
AdChoices