The SQL module is primarily used by the support team and advanced users; but it is also designed to generate your own information.
Copy the code in the section below and paste it into the Query box. Note: You can paste by doing 1) clicking CTRL+V or 2) Clicking on the Paste button in the Edit tab
- To show the result on the screen: Click on the Execute Query button
- To export the result into an Excel file: Click on the Save as Excel button
List of SQL tables
which will allow to create your own SQL scripts
Predefined Scripts
To generate a contact list, copy the following code: |
select congregations.congregation_name, congregations.servant_email, code_language.language from congregations, code_language where congregations.congregation_language=code_language.L_symbol order by code_language.language, congregations.congregation_name |
To find congregations that have already been validated, copy the following code: |
delete * from nativeValidated |
To export territory boundaries by geocodes, copy the following code: |
select terrnum, latitude, longitude from territoryBoundaries order by ID |
To list all territories in and out, with number of days out, copy the following code: |
select terrnum as [Territory], outDate as [Date given], pub as [Publisher Name], date()-outdate as [Number of Days] from activity where nameType=1 order by leftterrnum+rightterrnum |
To list when a territory was last checked-in, excluding events (such as memorial and conventions), copy the following code: |
select max(indate) as [Last checkin], terrnum as [territory number] from history where nz(event)='' group by terrnum order by terrnum |
List the territories that have not been worked in the last 6 months |
select max(indate) as [Last checkin], terrnum as [territory number] from history where nz(event)='' and terrnum in (select terrnum from activity where nz(outdate)='') and indate |