SQL

  SQL is located under the File tab, and Database grouping


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 

Download


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

Who's Online

We have 35 guests and no members online