Dynamic Dates

Dynamic dates in queries can be much more useful than explicit dates because they are based on the current date. The following methods handle common scenarios. The following examples use the Domain Time Token token, but could also use the User Time Token or GETDATE().

Seven Days Ago

This technique can be used to calculate a date N days ago, such as 7, 30, or 60 days ago.

dp_DomainTime - 7

Previous Calendar Month

MONTH([date_field]) = MONTH(DATEADD(m, -1, dp_DomainTime)) AND YEAR([date_field]) = YEAR(DATEADD(m, -1, dp_DomainTime))

Current Calendar Month

MONTH([date_field]) = MONTH(dp_DomainTime) AND YEAR([date_field]) = YEAR(dp_DomainTime)

Next Calendar Month

MONTH([date_field]) = MONTH(DATEADD(m, 1, dp_DomainTime)) AND YEAR([date_field]) = YEAR(DATEADD(m, 1, dp_DomainTime))

AVG

AVG returns the average value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset.

Basic Syntax

In its most basic form, the AVG returns the grand total for a field value in all records returned by the rest of the query.

AVG( <field-name> ) FROM <from-clause>

Example

The following field example returns the average donation for Donor.

(SELECT AVG(D.Donation_Amount) 
  FROM Donations D 
  WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]

Also see Microsoft Doc: AVG.

COUNT

COUNT returns the number of items in a group. The group is determined by the FROM clause that follows and may represent all records or distinct records. All is the default.

Basic Syntax

In its most basic form, the COUNT returns the total count of all records returned by the rest of the query. Using the asterisk (*) to represent the entire group is common because the database engine is able to easily optimize the query.

COUNT (*) FROM <from-clause>

Distinct Results

Use DISTINCT to return the number of unique non-null values returned by rest of the query.

COUNT ( DISTINCT <expression> ) FROM <from-clause>

Example

The following field example from a Households View will count the number of Contacts in each Household.

(SELECT COUNT(C.Contact_ID) 
  FROM Contacts C 
  WHERE C.Household_ID = Households.Household_ID) AS [Contact Count]

See View Examples: Groups Over Capacity, Groups Under Capacity, Events Needing Approval, and Group Counts. Also see Microsoft Doc: COUNT.

DATEADD

DATEADD alters a date by adding or subtracting units of time. It returns the altered date/time.

Syntax

DATEADD( <date-part>, <number>, <date> )

Examples

One year ago:

DATEADD(yy,-1,GETDATE())

This example uses weekday with a datediff to find the first of the week:

DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

See View Example: Expiring Product Option Prices. Also see Microsoft Doc: DATEADD.

DATEDIFF

DATEDIFF returns the difference between two dates.

Syntax

DATEDIFF( <date-part>, <start-date>, <end-date> )

Examples

How many months since a Participant's last attendance:

DATEDIFF(mm,[_Last_Attendance_Date],GETDATE())

This example uses weekday with DATEADD to find the first of the week.

DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

See View Example: Expiring Product Option Prices. Also see Microsoft Doc: DATEDIFF.

DATEPART

DATEPART returns part of a date as an integer.

Syntax

DATEPART( <date-part>, <date> )

Week/Weekday

The Week and Weekday uses the first day of the week set in SQL Server. The U.S. English default is Sunday, but it can be changed. To find the current setting you can use:

@@DATEFIRST

Because the value can be changed, getting the week or weekday is not always reliable. One way to do it reliably is to use DATENAME.

Examples

The year for the current date:

DATEPART(yy,GETDATE())

The Month for the current date:

DATEPART(mm, GETDATE())

Also see Microsoft Doc: DATEPART.

DATENAME

DATENAME returns part of a date as a string (text).

Syntax

DATENAME( <date-part>, <date> )

Example

The day of the week for the current date:

DATENAME(weekday,GETDATE())

For the date 1/1/2000, this would return "Saturday".

Also see Microsoft Doc: DATENAME.

GETDATE

GETDATE returns the current date and time.

Syntax

GETDATE()

Details

GETDATE can be modified by adding or subtracting whole days. For example, this is the date and time exactly seven days ago:

GETDATE() - 7

See View Examples: Contact Changes and Group Participants in Selection. Also see Microsoft Doc: GETDATE.

EXISTS

EXISTS checks if a query has any rows and returns True or False.

Syntax

EXISTS ( subquery )

Use

EXISTS is often used in the filter of Views or Dependent Clause of Processes to determine if the record in question has related records with specific values.

Example

In the following example, a View on Contacts with this filter will only return Contacts that are registered in an active Event:

EXISTS(SELECT 1 FROM Event_Participants EP 
  JOIN Events E ON E.Event_ID = EP.Event_ID 
  WHERE EP.Participant_ID = Contacts.Participant_Record 
    AND GETDATE() BETWEEN E.Event_Start_Date AND ISNULL(E.Event_End_Date,GETDATE()+1))

See View Examples: Contact Changes and Contacts With Milestones.

ISNULL

ISNULL tests a value for null and returns the value or a default if the value is NULL.

Syntax

ISNULL( <value>, <replacement-for-null> )

Details

For example, ISNULL can check for a null date and return a date. Here, this technique is combined with GETDATE to account for NULL Event End Dates in a between statement. If the Event_End_Date is NULL, the function uses tomorrow as the date instead:

GETDATE() BETWEEN Event_Start_Date AND ISNULL([Event_End_Date], GETDATE()+1)

See View Examples: Contact Changes, Group Participants in Selection, and Participant Heads of Household. Also see Microsoft Doc: ISNULL.

MAX

MAX returns the highest value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset.

Basic Syntax

In its most basic form, the MAX returns the maximum field value in all records returned by the rest of the query.

MAX( <field-name> ) FROM <from-clause>

Example

The following field example returns the earliest donation date for Donor:

(SELECT MIN(D.Donation_Date) 
  FROM Donations D 
  WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]

See View Example: Group Participant Form Submissions. See also Microsoft Doc: MAX.

MIN

MIN returns the lowest value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset. All is the default.

Basic Syntax

In its most basic form, the MIN returns the minimum field value in all records returned by the rest of the query.

MIN( <field-name> ) FROM <from-clause>

Example

The following field example returns the earliest donation date for Donor:

(SELECT MIN(D.Donation_Date) 
  FROM Donations D 
  WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]

See View Example: New Donors Last Week. Also see Microsoft Doc: MIN.

STUFF

The STUFF function inserts a string into another string. It is most valuable for returning a list of values from a query as a single value when combined with the "FOR XML PATH" clause.

Syntax

STUFF ( <text_expression> , <start> , <length> , <replace_with> )

Use

STUFF is often used on combination with FOR XML PATH to retrieve a comma-delimited list (see below).

STUFF...SELECT...FOR XML PATH — STUFF is often combined with "FOR XML PATH" to return a comma delimited list of values output as a single field. Here, the highlighted portion of the example represents a SELECT statement which returns a field (concatenated with a comma). The "FOR XML PATH" returns the results of the query as a single string. Using stuff in this way trims the excess comma from the query result.

Syntax

STUFF((SELECT ', ' + <field> FROM <tables> FOR XML PATH('')),1,2,'') AS [Field_Name]

See View Examples: All Event Rooms, Contact Changes, Participant Heads of Household, Contacts With Files Attached, Reports Permitted, Security Role Pages, Tasks Attached to Contacts, and Tools Permitted. Also see Microsoft Doc: STUFF.

SUM

SUM returns the total value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset.

Basic Syntax

In its most basic form, the SUM returns the grand total for a field value in all records returned by the rest of the query.

SUM( <field-name> ) FROM <from-clause>

Example

The following field example returns the earliest donation date for Donor:

(SELECT MIN(D.Donation_Date) 
  FROM Donations D 
  WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]

See View Examples: Household Donations, Top 50 Donors, and New Donors Last 7 Days. See also Microsoft Doc: SUM.