Question
Rolling 12 month data
I use the query below to give me summary data for three
categories, for each supplier number, base on a month and year
selection :
<cfquery name="qryGet_NORS" datasource="datasource">
select suplier_number,table2.supplier_name,
sum(case when date_sent is NULL and send_flag is NULL then 1 else 0 end) as cannot_send,
sum(case when date_sent is not NULL then 1 else 0 end) as total_send,
sum(case send_flag when 'No' then 1 else 0 end) as total_disapproved
FROM
table1 LEFT JOIN Master_Buyer_Table ON table1.buyer_number = Master_Buyer_Table.buyer_number
LEFT JOIN table2 ON table1.suplier_number = table2.supplier_number
where substring(convert(varchar,date_resolved,101), 1, 2) = '#form.selectmonth#'
and substring(convert(varchar,date_resolved,101), 7, 4) = '#form.selectyear#'
group by suplier_number,table2.supplier_name
</cfquery>
I then use this QoQ to give me the grand totals for each supplier number.
<cfquery dbtype="query" name="qryGet_Nors_sum">
select sum(cannot_send) as total_cannot_send,
sum(total_send) as total_total_send,
sum(total_disapproved) as total_total_disapproved
from qryGet_NORS
</cfquery>
What I need to do now is to get and output data for 12 months, starting with the input month and year. For example, if they enter March 2008, then the above query would give me the three totals for the period. But now, I need the totals for the next month, April 2008, then the totals for May 2008, etc, all the way to March 2009.
How would I modify the query to get all this information ?
<cfquery name="qryGet_NORS" datasource="datasource">
select suplier_number,table2.supplier_name,
sum(case when date_sent is NULL and send_flag is NULL then 1 else 0 end) as cannot_send,
sum(case when date_sent is not NULL then 1 else 0 end) as total_send,
sum(case send_flag when 'No' then 1 else 0 end) as total_disapproved
FROM
table1 LEFT JOIN Master_Buyer_Table ON table1.buyer_number = Master_Buyer_Table.buyer_number
LEFT JOIN table2 ON table1.suplier_number = table2.supplier_number
where substring(convert(varchar,date_resolved,101), 1, 2) = '#form.selectmonth#'
and substring(convert(varchar,date_resolved,101), 7, 4) = '#form.selectyear#'
group by suplier_number,table2.supplier_name
</cfquery>
I then use this QoQ to give me the grand totals for each supplier number.
<cfquery dbtype="query" name="qryGet_Nors_sum">
select sum(cannot_send) as total_cannot_send,
sum(total_send) as total_total_send,
sum(total_disapproved) as total_total_disapproved
from qryGet_NORS
</cfquery>
What I need to do now is to get and output data for 12 months, starting with the input month and year. For example, if they enter March 2008, then the above query would give me the three totals for the period. But now, I need the totals for the next month, April 2008, then the totals for May 2008, etc, all the way to March 2009.
How would I modify the query to get all this information ?
AdChoices