6 Replies Latest reply on Apr 16, 2014 3:06 AM by BKBK

    get data from a specific month?

    no_name_123 Level 1

      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

      pending.PNG