What would you like to know more about?

Understanding the T-SQL

You can perform advanced actions that help you get the most out of Ministry Answers.

Note: You must use Professional Services to get Ministry Answers installed on your system. They will coach you on how it works and create sample questions with you. If you get stuck, you can always reach out to Professional Services for help. Hourly rates apply.
Before you get started, here are a few things to know:
  • You must understand Transact-SQL, also known as T-SQL. This is different from the SQL-like conventions used in different areas of MinistryPlatform.
  • You must have access to the database server.

Throughout this article, we'll reference the following example (scroll within the box to see the full code block):

SELECT [MinistryWeekID]
   , [MinistryQuestionID]
   , COUNT(DISTINCT(EP.Participant_ID)) AS Number
   , Prog.Congregation_ID
   , Prog.Ministry_ID AS Ministry_ID
   , Prog.Program_ID AS Program_ID
   , 'Attendance' --Type
   , [DomainID]
FROM
   Event_Participants as EP
   INNER JOIN Events E on EP.Event_ID = E.Event_ID
   INNER JOIN Group_Participants GP ON EP.Group_Participant_ID = GP.Group_Participant_ID
   INNER JOIN Groups G ON G.Group_ID=GP.Group_ID
   INNER JOIN Programs Prog ON E.Program_ID=Prog.Program_ID
WHERE
	EP.Participation_Status_ID IN (3,4)
	AND G.Group_Type_ID=2
	AND E.Event_Start_Date BETWEEN [MinistryWeekStart] AND [MinistryWeekEnd]
   AND Prog.Ministry_ID = 5
GROUP BY Prog.Congregation_ID, Prog.Program_ID, Prog.Ministry_ID

Let's take a look at the code elements in this example.

SELECT Statement

The SELECT statement uses defined fields that generate a data point for the question you are asking. There is one data point per week. For monthly and annual reporting, the weekly data is aggregated and displayed in the pages and views.
  • [MinistryWeekID] – Identifies the week that the answer will apply to. Required token.
  • [MinistryQuestionID] – Identifies the question that will be answered. Required token.
  • "Number" – The value you are looking for as an answer to your question. You can aggregate values using standard SQL options, such as sum(), min(), max(), or avg().
  • "Prog.Congregation_ID" – To report answers based on congregations, your SQL must include the Congregation ID field. If you don't need the congregation for this question, enter NULL in this field.
  • Ministry_ID – To report answers based on Ministries, your SQL must include the Ministry_ID field. If you don't need the ministry for this question, enter NULL in this field.
  • Program_ID – To report answers based on programs, your SQL must include the Program_ID field. If you don't need the program for this question, enter NULL in this field.
  • "Type" – Use this free-form field to group answers to different (but related) questions. For example, you may have three questions that gather attendance data for adults, youth, and children. If you set this field to Attendance, then you could create a view for "All Attendance Answers" that uses this field in the filter clause. If you don't want to do this, enter NULL in this field.
  • [DomainID] – Identifies the domain this question applies to. Required token.
    Warning: Do not change or modify this field.

FROM Clause

The FROM clause gets the data you're looking for. In our example, we start in Event_Participants because that's where we can find attendance information. To get details on the event and the person attending, we must also JOIN the tables for Events, Group_Participants, Groups, and Programs.

WHERE Clause

The WHERE clause filters the data down to what you're looking for. In our example, the [MinistryWeekStart] and [MinistryWeekEnd] tokens define the date range for your answer data. Remember, Ministry Answers collects data in weekly snapshots, and these weeks are configured in the Ministry_Weeks table. These correspond to the [MinistryWeekID] in the SELECT statement. An example start and end date would be 2021-03-03 00:00:00 TO 2021-03-09 23:59:59.

Different Date Ranges

You may want to know something like the total offerings in the last year. The data you collect each week is a sum of all offerings over the last year, which means you can use the weekly data like a yearly moving average. This works well when looking at the weekly answers. But when looking at the monthly or yearly period data, you can set the Value Aggregation field to something other than "sum". For example, you may want an AVG of all points over the time period. Or, you may want a MIN (for the lowest point in the trend) or MAX (for the highest point in the trend).

This also requires you to set your [MinistryWeekStart] and [MinistryWeekEnd] tokens differently. For our example, it might look something like this:
AND E.Event_Start_Date BETWEEN dateadd(dd, -365,[MinistryWeekEnd]) AND [MinistryWeekEnd]

GROUP BY Clause

Because we are aggregating data, we need to group the data to know the bounds of our sum(), min(), and so on. If you have program, congregation, and/or ministry in your query, you must add them to the GROUP BY clause in the order you want them. Our example will group everything by congregation, then by program, then by ministry.