7 Replies Latest reply on Jun 11, 2009 10:24 AM by CFmonger

    Automatically eliminating a record at the begining of a new month

    CFmonger

      Hello;

      I have a little app I am building that makes a list of records organized by the month the record falls in. this is how it looks:

       

      JUNE
      Record title goes here as a link
      record date
      the location of the event

       

      <!--- all the records for JUNE fall in here, the link goes to a page with all the details --->


      JULY
      same thing as June

       

      and so on....Now as it is the code works fine, BUT when lets say it goes to JULY, the JUNE records are still visible, I want them to go away after the month passes. Is that possibly an easy task using the code I wrote for this? The don't have to be deleted from the DB, but they need to "disappear" in the web site when the month passes.

       

      This is the code:
      <cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
      SELECT DISTINCT(YEAR(eventDate)) AS gTempx2, display
      FROM events
      WHERE display = 0 AND YEAR(eventDate) >= #dateFormat(now(), 'yyyy')# ORDER BY YEAR(eventDate) ASC
      </cfquery>
      <cfset distintYearEventDate = DateFormat(now(), "yyyy")>


      <cfset tempdx = DateFormat(now(), "mm/dd/yy")>
                      <cfif DateFormat(now(), "mm/dd/yy") GTE "#tempdx#">
                        <cfoutput query="gTempx1">
                          <cfset gTempx5 = "#gTempx1.gTempx2#">
                          <cfif DateFormat(now(), "mm/dd/yy") GTE "#tempdx#">
                            <cfset gTempx6 = DateFormat(now(), "yyyy")>
                            <cfset gTempx7 = DateFormat(now(), "mmmm")>
                            <cfset distintYear = DateFormat(now(), "yyyy")>
                          </cfif>
                          <cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
                            SELECT DISTINCT(MONTH(eventDate)) AS gTempx4, display
             FROM events
             WHERE display = 0 AND YEAR(eventDate) = #gTempx5#
                            ORDER BY MONTH(eventDate) ASC
                          </cfquery>
                          <cfset dateAdd = DateFormat(now(), "mmmm")>
                          <cfset thirtyTempx1 = "#dateFormat(dateAdd('d',-30,Now()),'mm/dd/yyyy')#">
                          <cfloop query="gTempx3">
                            <cfset gTempx6 = "#gTempx4#/01/#gTempx5#">

       

      <b><u>#ucase("#DateFormat(gTempx6,'mmmm YYYY')#")#</u></b>

       

      <cfquery name="GetRecordevent" datasource="#APPLICATION.dataSource#">
            SELECT events.title AS ViewField2, events.eventDate AS ViewField3, events.location AS ViewField5, events.ID AS ID
            FROM events
            WHERE display = 0 AND YEAR(eventDate) = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
            ORDER BY DAY(eventDate) ASC
                            </cfquery>
                            <cfloop query="GetRecordevent">

      <a href="detail.cfm?ID=#ID#" class="subNav">

      #ViewField2#

      #dateformat(ViewField3, "mmm-dd-yyyy")#<br>
      Location: #ViewField5#

      </cfloop>
      </cfloop>
      </cfoutput>
      </cfif>

       

      can anyone help me out? Don't know what to tweekout on this to make it rotate through the months like a normal calendar.

       

      thank you

      CFmonger

        • 1. Re: Automatically eliminating a record at the begining of a new month
          davidsimms Level 1

          Select only those records from July forward in your SQL statement.

          • 2. Re: Automatically eliminating a record at the begining of a new month
            CFmonger Level 1

            How would I write that into my where statement as it is? I did some changes in the code and got ride of some old functions that are not being used any longer.Would I add mm, Now() to the where statement?

             

            These are th query's now. there are 3 of them, and this change will have to effect all 3, I believe.

             

            Query 1:

             

            <cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
                     SELECT DISTINCT(YEAR(eventDate)) AS gTempx2
                     FROM events
                     WHERE YEAR(eventDate) >= #dateFormat(now(), 'yyyy')#
                     ORDER BY YEAR(eventDate) ASC
            </cfquery>

             

            Query 2

             

            <cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
                   SELECT DISTINCT(MONTH(eventDate)) AS gTempx4
                   FROM events
                   WHERE YEAR(eventDate) = #gTempx5#
                   ORDER BY MONTH(eventDate) ASC
            </cfquery>

             

            Query 3

             

            <cfquery name="GetRecordevent" datasource="#APPLICATION.dataSource#">
                  SELECT events.title AS ViewField2, events.eventDate AS ViewField3, events.location AS ViewField5, events.ID AS ID
                  FROM events
                  WHERE YEAR(eventDate) = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
                  ORDER BY DAY(eventDate) ASC
            </cfquery>

             

            So would I add something like this to my where:

            WHERE YEAR(eventDate) >= #dateFormat(now(), 'yyyy','mm')#

             

            I know that isn't right, I am not distinguishing the month now()

            • 3. Re: Automatically eliminating a record at the begining of a new month
              davidsimms Level 1

              I only glanced over your code, but I don't understand why the YEAR() function exists in your queries given what you've described you're trying to do. Getting rid of that it seems would give a desirable outcome.

              • 4. Re: Automatically eliminating a record at the begining of a new month
                CFmonger Level 1

                So I should change it to this:

                 

                                   SELECT DISTINCT(MONTH(eventDate)) AS gTempx2
                                   FROM events
                                   WHERE MONTH(eventDate) >= #dateFormat(now(), 'mm')#
                                   ORDER BY MONTH(eventDate) ASC

                 

                then change my cfset to

                <cfset distintMonthEventDate = DateFormat(now(), "mm")>

                 

                or is there more then this?

                 

                thanks

                 

                CF

                • 6. Re: Automatically eliminating a record at the begining of a new month
                  CFmonger Level 1

                  ok, I am adding that in, but now it isn't showing anything on the page, not an error either.

                   

                  this is what I did:

                   

                  q1

                  <cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
                                     SELECT DISTINCT(YEAR(eventDate)) AS gTempx2
                                     FROM events
                                     WHERE eventDate > #NOW()#
                                     ORDER BY YEAR(eventDate) ASC
                                  </cfquery>

                   

                  q2

                  <cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
                                        SELECT DISTINCT(MONTH(eventDate)) AS gTempx4
                         FROM events
                         WHERE eventDate > #NOW()# = #gTempx5#
                                        ORDER BY MONTH(eventDate) ASC
                                      </cfquery>

                   

                  q3

                  <cfquery name="GetRecordevent" datasource="#APPLICATION.dataSource#">
                                          SELECT events.title AS ViewField2, events.eventDate AS ViewField3, events.location AS ViewField5,
                        events.ID AS ID
                        FROM events
                        WHERE YEAR(eventDate) = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
                        ORDER BY DAY(eventDate) ASC
                                        </cfquery>

                   

                  not sure what to do in q3, all 3 depend on each other.

                  do I use the query I showed with the distinct month and change all the years to month and add your query?
                  kind of tried that and so far not working

                  • 7. Re: Automatically eliminating a record at the begining of a new month
                    CFmonger Level 1

                    I am getting someplace now.
                    I have been messing with the where statement. I have it so it will eliminate the previous months records, but it is duplicating the records now and makeing them for lets say I have a record is set for July 2009 that same record will appear as July 2010 as well.

                     

                    This is what I changed my 2 querys to:

                     

                    q1

                    <cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
                                    SELECT DISTINCT(YEAR(eventDate)) AS gTempx2, display
                                    FROM events
                                    WHERE YEAR(eventDate) >= #dateFormat(now(), 'yyyy')#
                        ORDER BY YEAR(eventDate) ASC
                                   </cfquery>

                     

                    q2.

                    <cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
                                          SELECT DISTINCT(MONTH(eventDate)) AS gTempx4, display
                           FROM events
                           WHERE eventDate > #NOW()#
                                          ORDER BY MONTH(eventDate) ASC
                                        </cfquery>

                     

                    q3.

                     

                    <cfquery name="GetRecordevent" datasource="#APPLICATION.dataSource#">
                                            SELECT events.title AS ViewField2, events.eventDate AS ViewField3, events.eventTime
                                            AS ViewField4, events.location AS ViewField5, events.ID AS ID
                          FROM events
                          WHERE eventDate > #NOW()# = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
                          ORDER BY DAY(eventDate) ASC
                                          </cfquery>

                     

                    can anyone help me fix this so it works properly. for some reason it is making the same records appear every year.