home

MinistryPlatform

RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite
RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite

What would you like to know more about?

Show Contents

thumbnailMinistryPlatform

SQL and Databases

SQL Functions

A list of common SQL functions you can use in the Platform.

Date Functions

Text Functions

General Functions

Aggregate Functions

  • AVG (Average)
  • COUNT
  • MIN
  • MAX
  • SUM (Total)

Subquery Functions

  • COUNT
  • EXISTS
  • STUFF

Function Details

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 you could also use the User Time Token or GETDATE().

Note: In all examples, replace [date_field]'s with the specific date field you are using (such as Contact [Date_of_Birth] or [Event_Start_Date]).

Seven Days Ago

Use this technique 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>
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]

See also: 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. It's common to use the asterisk (*) to represent the entire group because the database engine can 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>
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.

See also: Microsoft Doc: COUNT.

DATEADD

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

Syntax

DATEADD( <date-part>, <number>, <date> )
Details
  • Date Part – Determines the type of unit and number to be added.
  • Number – The number of units. Use this to add or subtract years, months, hours, and so on. To subtract, use a negative value.
  • Date – The date to alter.
Date Parts
  • Year – yy (or yyyy)
  • Month – m (or mm)
  • Hour – hh
  • Minute – mi (or n)
  • Quarter – q (or qq)
  • Day of Year – dy (or y)
  • Week – wk (or ww)
  • Weekday – w (or dw)
  • Day – d (or dd) For adding days, you can just add whole integers to a date. This has the same effect and is more efficient.
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.

See also: Microsoft Doc: DATEADD.

DATEDIFF

DATEDIFF returns the difference between two dates.

Syntax

DATEDIFF( <date-part>, <start-date>, <end-date> )
Details
  • Date Part – Determines the type of units to calculate between dates (see below).
  • Start Date – The first date in the range.
  • End Date – The second date in the range.
Date Parts
  • Year – yy (or yyyy)
  • Month – m (or mm)
  • Week – ww (or wk)
  • Hour – hh
  • Minute – mi (or n)
  • Quarter – q (or qq)
  • Day of Year – dy (or y)
  • Weekday – w (or dw)
  • Day – d (or dd)
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.

See also: Microsoft Doc: DATEDIFF.

DATEPART

DATEPART returns part of a date as an integer.

Syntax

DATEPART( <date-part>, <date> )
Details
  • Date Part – Determines the type of units to extract from the date (see below).
  • Date – The date to retrieve a part from.
Date Parts
  • Year – yy (or yyyy)
  • Month – m (or mm)
  • Day – d (or dd)
  • Hour – hh
  • Minute – mi (or n)
  • Quarter – q (or qq)
  • Day of Year – dy (or y)
  • Week – wk (or ww)
  • Weekday – dw (or weekday)

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 you can change the value, it's not always reliable to get the week or weekday. One way to do it reliably is to use DATENAME.

The year for the current date: 
DATEPART(yy,GETDATE())

The Month for the current date:

DATEPART(mm, GETDATE())

See also: Microsoft Doc: DATEPART.

DATENAME

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

Syntax

DATENAME( <date-part>, <date> )
Details
  • Date Part – Determines the type of units to extract from the date (see below).
  • Date – The date to retrieve a part from.
Date Parts
  • Year – yy (or yyyy)
  • Month – m (or mm)
  • Day – d (or dd)
  • Hour – hh
  • Minute – mi (or n)
  • Quarter – q (or qq)
  • Day of Year – dy (or y)
  • Week – wk (or ww)
  • Weekday – dw (or weekday)
The day of the week for the current date: 
DATENAME(weekday,GETDATE())

For the date 1/1/2000, this returns "Saturday".

See also: Microsoft Doc: DATENAME.

GETDATE

Tip: Use the Domain Time Token (dp_DomainTime) rather than GETDATE() for best results.

GETDATE returns the current date and time.

Syntax

GETDATE()

Details

Modify GETDATE 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.

See also: Microsoft Doc: GETDATE.

EXISTS

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

Syntax

EXISTS ( subquery )

Use

Use EXISTS in the filter of views or dependent clause of processes to determine if the record in question has related records with specific values.

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

ISNULL returns one of two values:
  • If the first parameter IS NOT NULL, it will be returned.
  • If the first parameter IS NULL, the second parameter will be returned.
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.

See also: 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>
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>
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.

See also: Microsoft Doc: MIN.

STUFF

The STUFF function inserts a string into another string. It is most valuable to return 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. In the syntax below, the SELECT statement 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.

See also: 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>
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.

portal
MinistryPlatform

Last updated: November 26, 2024