Skip to main content
Inspiring
June 10, 2009
Answered

Automatically eliminating a record at the begining of a new month

  • June 10, 2009
  • 1 reply
  • 1383 views

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

This topic has been closed for replies.
Correct answer davidsimms

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


WHERE eventDate > #NOW()#

1 reply

davidsimms
Inspiring
June 11, 2009

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

CFmongerAuthor
Inspiring
June 11, 2009

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()

davidsimms
Inspiring
June 11, 2009

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.