Have you ever wanted to create a list that is ordered and displayed by date, but age the records so that only the last 30 days display? Perhaps a list like "Users Added In The Last 30 Days". In my case I have the ability for members to note a significant life announcement like a birth or marriage. Since they are not likely to remove it in a timely fashion, I would like to prevent any that are older than 180 days from displaying.
SQL has many powerful capabilities within query statements. Small pieces of programmatic code can be inserted in query statements - this tip takes advantage of that capability. MYSQL will automatically handle your date field correctly whether it is a date time field like "Member Since" or just a date field such as a CB custom date field. Here is how you create a list that automatically stops displaying records that are a certain number of days older than the current date. For this example we will display member records created in the last 30 days.
Detailed Steps
Create a new list and ensure that it is sorted in descending order by the date field that you are intrested in aging the records by. This date field does not actually have to be displayed in your list - but in most cases it helps users understand that the list is somehow date dependent. For the "Filter" select "Advanced" and place this code in field:
DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= `registerDate`DATE_SUB(CURDATE(), INTERVAL 30 DAY) defines a small subroutine that filters all dates including today and 30 days in the past.
<= `registerDate` "pushes" the registerDate field into this code fragment. You can also combine the date filter with any other criteria as well - here is one that shows records where a custom annoucement text field is not blank and the custom announcement date field is not older than 60 days:
`cb_announcementtextcustomfield` != '' and DATE_SUB(CURDATE(), INTERVAL 60 DAY) <= `cb_announcement datecustomfield`
I am discovering that there are many other cool things that can be done via SQL queries.
Please rate this article so we know what types of Tips and Tricks are most helpful to you!
| {mos_sb_discuss:7} |