Understanding the T-SQL
You can perform advanced actions that help you get the most out of Ministry Answers.
- You must understand Transact-SQL, also known as T-SQL. Note that 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
- [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. 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/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).
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.