How old is old? Here's a handy SQL tip for determining the approximate age of your netFORUM database.

by Andrew Vincent | Feb 25, 2016 | netFORUM

As your netFORUM instance ages and sites get copied and restored, it can be difficult to keep track of how old your database is. Knowing the approximate age of your database can be critical information to know when testing issues, working through complex processes that require up-to-date member data, or researching the history of a particular transaction.

A very helpful SQL command that you can use to find the age of your database is MAX. Keep in mind that all tables include both an Add Date and a Change Date as part of the database’s metadata.

Whenever a record is added or updated through the interface, netFORUM will automatically update these columns with the appropriate time stamp. By targeting this data point, we can find a reasonable estimate of how old the database is.

If you are able to work directly with your database through SQL Studio, you may copy and paste the SQL below:

--For Most Recent record added:
select max(distinct cst_add_date)
from co_customer (nolock)

or

--For Most Recent record updated:
select max(distinct cst_change_date)
from co_customer (nolock)

The Customer table referenced above represents Individuals, Organizations, Chapters, and Households.

If you are not able to work with SQL Studio, you may still be able to get this information through the iWeb Query Tool.

Using the “Is In” operator, the access point to sub-queries, you are able to add SQL commands directly into the query string.

In the screenshot below, note that the select statement was modified to fit into the Query - Individuals group item.

Database Age Query Individual

The modified SQL that was used here is as follows:
select max(distinct ind_add_datefrom co_individual (nolock)

If you wanted to use this query for the Change User you would only need to modify the column name:
select max(distinct ind_change_datefrom co_individual (nolock)

And as a final note, if you wanted to search for the age of organization, chapters, or household records, you would need to adjust both the column name and the table name to the appropriate names. Organizations would use the org_change date, chapters would use the chp_change_date, and household records would use the cst_change_date (same table as the original search).

For example, the organization query would look like the screenshot below:

netFORUM Database Age Organization Query

In all of these cases, you must ensure that the appropriate column is returned in your result set. Remember that the columns are set under the Query Columns tab.

netFORUM Database Age: columns under Query Column Tab

For more information on the syntax available for the MAX command, read this article from the horse’s (Microsoft’s) mouth.