get data from a specific month?

[no_name_123]

I have this code:

<h1><cfoutput>#self#</cfoutput></h1>

<cfquery datasource="Intranet" name="month_data">

  select * from CSEReduxResponses where status=1 and execoffice_status=0

   AND MONTH([approveddate]) = MONTH(GETDATE())

   </cfquery>

<cfquery datasource ="Intranet" name="GetDeptSubmissions">SELECT * FROM CSEReduxResponses</cfquery>

<cfquery dbtype="query" name="GetApproved">SELECT * FROM GetDeptSubmissions WHERE status = 1 AND execoffice_status = 1</cfquery>

<cfquery dbtype="query" name="GetDenied">SELECT * FROM GetDeptSubmissions WHERE status = 1 AND execoffice_status = 2</cfquery>

<cfif month_data.RecordCount gt 0>

<cfoutput>

          <h2>Comments / Ratings Administration</h2>

          <div>

                    <div class="display_count pending_outer">

                              <div class="display_count_desc pending_inner">Pending</div>

                              <cfif month_data.RecordCount gt 0><a href="cse_execoffice_pending.cfm"></cfif>

                                        <span class="display_count_number">#month_data.RecordCount#</span>

                              <cfif month_data.RecordCount gt 0></a></cfif>

                    </div><!--- /div class="display_count" --->

                    <div class="display_count approved_outer">

                              <div class="display_count_desc approved_inner">Approved *</div>

                              <cfif GetApproved.RecordCount gt 0><a href="cse_execoffice.cfm?approved"></cfif>

                                        <span class="display_count_number">#GetApproved.RecordCount#</span><br>

                              <cfif GetApproved.RecordCount gt 0></a></cfif>

                    </div><!--- /div class="display_count" --->

                    <div class="display_count denied_outer">

                              <div class="display_count_desc denied_inner">Denied</div>

                              <cfif GetDenied.RecordCount gt 0><a href="cse_execoffice.cfm?denied"></cfif>

                                        <span class="display_count_number">#GetDenied.RecordCount#</span><br>

                              <cfif GetDenied.RecordCount gt 0></a></cfif>

                    </div><!--- /div class="display_count" --->

          </div>

</cfoutput>

</cfif>

Right now it display the the output if the 'month_data.recordcount gt 0' , but this would be for all the

data from the current month because of the query 'month_data'

What I want to do display_count for each month.

for example right now it display the data from April, but once its May I would like to display another

'display_count' from the month of March, even if the other Pending from April still has not hit zero(0).

any help/advice will help, not sure if there is a better way to do this.

this is a example of what i have right now, this has 3 peding in the month of April

[WolfShade]

Is this just for tallying purposes?  Or does this also retrieve the relevant data?  If it's just for tallying totals, you can use COUNT(columnName) instead of getting all the records and displaying a recordcount.

If you want to get all of the data for, say, two months prior, you can make a query for each month, and use DateDiff for one and two months, respectively, based upon the current month of getDate().

^_^

[no_name_123]

If you want to get all of the data for, say, two months prior, you can make a query for each month, and use DateDiff for one and two months, respectively, based upon the current month of getDate().

do you mean make a query for all the month(12 months)

[WolfShade]

no_name_123 wrote:

do you mean make a query for all the month(12 months)

If that's how many months you want to fetch, that's one way of doing it.  OR, you could do a UNION query for each month, give each an additional column called GROUPING and assign it the value of the month (1-12) and use CFOUTPUT GROUPBY="grouping" to sub output each month.

V/r,

^_^

[Carl Von Stetten]

It looks like you've posted this same or related question twice on StackOverflow (http://stackoverflow.com/questions/22970021/how-can-i-show-a-output-from-a-specific-date and http://stackoverflow.com/questions/23092245/how-to-output-different-data-from-different-months).  If you're going to cross-post to multiple forums, then mention that and provide links to your posts on other forums so people can see what others are suggesting and not duplicate efforts.

-Carl V.

[BKBK]

Carl Von Stetten wrote:

It looks like you've posted this same or related question twice on StackOverflow (http://stackoverflow.com/questions/22970021/how-can-i-show-a-output-fr om-a-specific-date and http://stackoverflow.com/questions/23092245/how-to-output-different-da ta-from-different-months).  If you're going to cross-post to multiple forums, then mention that and provide links to your posts on other forums so people can see what others are suggesting and not duplicate efforts.

Plus http://forums.adobe.com/message/6295752#6295752

@No_name_123

As I suggested elsewhere, I hope you will spend more time doing the code than explaining your requirements for fellow developers to do the code for you. I would avoid saying things like these:

... What I want to do display_count for each month....

... I would like to display another 'display_count' from the month of March...

Instead, I would write some code, or even pseudocode, and then ask for suggestions on it.

Message was edited by: BKBK

[BKBK]

My suggestion is similar to WolfShade's. You already have current month = MONTH(GETDATE()). Assuming SQL Server, you may need the following SQL :

Two months ago = MONTH(DATEADD(MONTH,-2,GETDATE()))

Last month = MONTH(DATEADD(MONTH,-1,GETDATE()))

Next month = MONTH(DATEADD(MONTH,1,GETDATE()))