Trail Blazer Knowledge Base

 

Home : Queries : Advanced Search Queries - Using The SQL Tab - Advanced Report Requests from Customers

Knowledge Base







User:

Password:



Article ID: KB169
Keyword Name: SQL, Querying, Targeting, Advanced, Report, Locating, Searching, Data, Mining, Filter
Created: October 24, 2017
Viewed: 19100

Advanced Search Queries - Using The SQL Tab - Advanced Report Requests from Customers




Click Here to view this article as a PDF.




Author: Kristenson, Joel

Content Provided By: Panger, Mark & Piatt, Kevin

Last Updated: 2017-10-24






Overview


This article provides a list of useful sql queries (searches) you can run by using the SQL tab (advanced tool) in Trail Blazer.  Almost all of the queries in this article are ones that can’t be run using the regular input tabs at the top of the window you’re searching in.


Most of these queries are requests from customers over the years, if you don’t see one that you want, please contact our tech support team 1-866-909-8700.  This article is updated periodically with new SQL query examples, so check back often.



http://www.us-cert.gov/sites/default/files/images/tip-icon.pngTip: Once you build a search that you run often you can save it as a dynamic favorite for future use (other users will also have access to it).  99% of the time a search query will go hand-in-hand with a custom format, read this article to learn how to build and save formats.






How to Use the SQL Tab (Advanced Search Tool)


The Trail Blazer query engine allows you to query ‘nearly’ everything stored in the database.  But there are some specialized columns and some specialized tasks for which the query engine won’t work as provided.


Fortunately we have a tab named SQL.  This tab allows you to enter criteria not available through the standard query panels.  For any SQL users out there, this tab is limited to the WHERE clause.  No other query elements are allowed.


All Trail Blazer columns are identified as CLxxxxxx and all tables are identified as TBxxxxxx where CL means column and TB means table.


At the top of each search window i.e. Donors/Voters (Contacts), Contributions, Addresses, Households, etc. there is a tab called SQL.  Click on that tab, and copy-and-paste the SQL code provided in the different sections below.  Ex:







Queries from the Voters/Donors/Contacts List


The following queries are entered from the Voters / Donors / Contacts list:





/* LYBUNT (Donor Gave Last Year But Unfortunately Not This */


(EXISTS (SELECT CL010562 FROM TB010611 WHERE ((CL010595=TB008485.CL005500) AND ((CL010568>=(select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106))) AND (CL010568<(select CONVERT(varchar,dateadd(ms,-1,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)))))))

AND

(NOT (EXISTS (SELECT CL010562 FROM TB010611 WHERE ((CL010595=TB008485.CL005500) AND ((CL010568>=(select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106))) AND (CL010568<(select CONVERT(varchar,dateadd(ms,-1,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106))))))))




/* SYBUNT (Donor Gave Some Year But Unfortunately Not This */


(EXISTS (SELECT CL010562 FROM TB010611 WHERE ((CL010595=TB008485.CL005500) AND ((CL010568<(select CONVERT(varchar,dateadd(ms,-1,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)))))))

AND

(NOT (EXISTS (SELECT CL010562 FROM TB010611 WHERE ((CL010595=TB008485.CL005500) AND ((CL010568>=(select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106))) AND (CL010568<(select CONVERT(varchar,dateadd(ms,-1,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106))))))))




/* New Donors Last Year */


(( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 >=(select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106))) AND ( CL010568 <=(select CONVERT(varchar,dateadd(ms,-1,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106))))))))

AND

(NOT (( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 <(select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)))))))))




/* New Donors Last Month */


(( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 >=( select  CONVERT ( varchar,dateadd ( d,- ( day ( dateadd ( m,-1,getdate () -2 ))) ,dateadd ( m,-1,getdate () -1 )) ,106 ))) AND ( CL010568 <=( select  CONVERT ( varchar,dateadd ( d,- ( day ( getdate ())) ,getdate ()) ,106 ))))))))

AND

(NOT (( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 <(select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)))))))))




/* New Donors This Month */


(( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 >=(select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106))) AND ( CL010568 <=(select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106))))))))

AND

(NOT (( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 <(select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)))))))))




/* New Donors This Year */


(( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 >=(select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106))) AND ( CL010568 <=(select CONVERT(varchar,dateadd(ms,-1,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106))))))))

AND

(NOT (( EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE (( CL010595 = TB008485.CL005500 ) AND (( CL010568 <(select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)))))))))




/* Donors Designated as Matching Employee Donations */


(EXISTS (SELECT CL046762 FROM TB046761 WHERE ((CL046763=CL005500) )))




/* Voters or Donors (Contacts) Whose Record Contain a Profile Image */


CL050210 is not null




/* Voters or Donors with ANY Relationship Defined */


(EXISTS (SELECT CL043217 FROM TB043206 WHERE (CL043217=CL005500)))




/* Voters having More than One Lawn Sign Record (Could be Multiple Locations or Could Be Multiple Elections) */


(Select count(*) from tb009955 WHERE (CL009957=TB008485.CL005500)) >1




/* Voters or Donors (Contacts) having More than One Address Assigned */


(SELECT COUNT (*) FROM tb044698 WHERE CL044743=CL005500)  >= 2




/* Variations on Voters/Donors (Contacts) either Having or Not Having an Address */


-- Home Address Exists:

CL044757 IS NOT NULL


-- Work Address Exists:

CL044758 IS NOT NULL


-- No Address:

(CL044757 IS NULL AND CL044758 IS NULL)


-- No Mailing or No Default Display, but Does Have Either Home or Work Address


(addressmailingid is null or addressdefaultdisplayID is null) and (addressmainhomeid is not null or addressmainworkid is not null)


-- Yes, even though the query above does not reference CLxxxxxx and TBxxxxxx values, it will still work.




/* Address Exists and Must Contain a Street, City, State, and Zip */


-- Address Must Exist


( CL044757  IS  not NULL  or  CL044758  IS not  NULL ) 

and


-- And Address Must be Complete (Default Display)


(EXISTS (SELECT * FROM TB044698 INNER JOIN TB044697 ON CL044699=CL044741 WHERE (((((((CL044707 > '') AND (CL044700 > '')) AND (CL044706 > '')) AND (CL044709 > '')) AND (CL044743=TB008485.CL005500))) AND (CL044787= 0) AND (CL044786=1)) ))


-- This query can also be used as a NOT condition where you are looking for people with incomplete addresses.




/* Search for Home ID Not Matching Voter Main Home ID */


(EXISTS (SELECT * FROM TB044698 INNER JOIN TB044697 ON CL044699=CL044741 WHERE ((((CL044699 <> CL044757) AND (CL044743=CL005500)) AND (CL044790=1)) AND (CL044787= 0))))


-- These can cause a problem for FEC reporting when the address flags are not set correctly.




/* Voters or Donors who have a Note on ANY Attribute */


((SELECT COUNT (*) FROM TB043304 WHERE ((TB043304.CL043307=TB008485.CL005500) AND ( CL043308 <> ''))) >= 1)




/* Voters or Donors Who Have Contributed In The Last 365 Days (Rolling) */


(EXISTS ( SELECT  CL010562  FROM  TB010611  WHERE ((( CL010595 = TB008485.CL005500 ) AND cl010568 > Convert(datetime, Convert(int, GetDate()-365  ))  ))))


-- The 365 can be changed to any value.  You may want those who contributed in the past 30 days or 180 days as examples.


-- This will NOT give you the amounts.  You would use the Format button for that information.




/* Who Are My NEW Contributors This Week (Month, Quarter, Year) */


((EXISTS (SELECT CL010562 FROM TB010611 WHERE (((CL010595=TB008485.CL005500) AND CL010796 IN (1)) AND ((CL010568>=

Convert(datetime, Convert(int, GetDate()-7  ))  )

)))) AND (NOT (EXISTS (SELECT CL010562 FROM TB010611 WHERE (((CL010595=TB008485.CL005500) AND CL010796 IN (1)) AND ((CL010568<

Convert(datetime, Convert(int, GetDate()-7  ))     )))))))


-- When saved as a Favorite, you can always find your new donors with a single click.  The number 7 can be set to any value.


-- NOTE – time is meaningful.  This query looks at the TIME the contribution was made as well as the date.




/* Voters/Donors Who Are Subscribed to Email But Are Not Enabled For Sending */


((((TB008485.CL009873 > '') AND (TB008485.CL009902=1)) OR ((TB008485.CL009907 > '') AND (TB008485.CL009908=1))) AND ( CL046318 = 0 ))




/* Voters/Donors (Contacts) Who Did NOT Attend an Event After Initial RSVP */


(EXISTS (SELECT CL011282 FROM TB011278 WHERE ((CL011284=TB008485.CL005500)  and cl047188= 47219 )))


/*

cl047188= 47219 = No

cl047188= 47218 = Yes

cl047188= 47220 = Maybe

*/


-- If you want to find those who DID attend, change the 47219 to 47218 as shown in the comments above.  This query would normally be used in addition to the Events tab since it will query ALL events for actual attendance assignments. --




/* Multi-Person Households Where There Are LEAST Two People Of The Same Party */


This query assumes you have previously run the Create Households Records process from the System Manager->Tools menu.  This query ignores single member households.


( SELECT  COUNT ( CL005500 )

FROM  TB005498 

WHERE ((( CL005529 = 'REP' ) AND  CL005500  IN 

 ( SELECT  CL005500  FROM  TB005498  WHERE ( CL008443 = CL008472 ))))) >= 2


-- REP is republican, DEM is democrat, you can modify this as needed (you may have other part types as well).  You may need to use the party drop down to see what other party values you would like to use – you can find these under the General > Profile tab. --




/* Multi-Person Households Where There Are At LEAST Two People Older Than 60 */


The dates need to be adjusted to accommodate the day you are running this query.  The date is referenced 3 times:


( SELECT  COUNT ( TB005498.CL005500 )
FROM  TB005498
WHERE
TB005498.CL008443 = TB008485.CL008443
and
 ((
 (TB008485.CL005528 <= '02/15/1953')
 and (TB005498.CL005528 <= '02/15/1953')
 AND  CL005500  IN
 ( SELECT  CL005500   FROM  TB005498  WHERE ( CL008443 = CL008472 )   and  (TB008485.CL005528 <= '02/15/1953')  
 )
 ))
)  >= 2




/* Households Contributed One Year, Not the Next, and Have No Open Pledges */


This is a long query broken into parts.


The reddish block says the HOUSEHOLD has to have contributed in 2010.


The orange blocks the HOUSEHOLD did NOT contribute in 2011.


The purple block says the HOUSEHOLD has NO open pledges.


((((( SELECT  SUM ( CL010666 )

FROM  TB010611 

WHERE ((( CL010796 <= 1 ) AND  CL010595  IN 

 ( SELECT  CL005500  FROM  TB005498  WHERE ( CL008443 = CL008472 )))


 -- HH contribution of at least $50 in the year 2010

 AND ((( CL010568 >= '01/01/2010' ) AND ( CL010568 < '1/1/2011' )))))> 50 ) AND


( --  HH  has  NO  contributions  in  2011

 ( SELECT  COUNT ( CL010666 ) FROM  TB010611  WHERE ((( CL010796 <= 1 ) AND  CL010595  IN ( SELECT  CL005500  FROM  TB005498  WHERE ( CL008443 = CL008472 ))) AND

 ((( CL010568 >= '01/01/2011' ) AND ( CL010568 < '1/1/2012' )))))= 0 ))


AND (NOT ( --  HH  has  NO  open  pledges

 ( EXISTS ( SELECT  *  FROM  TB005498  AS  TB046336  WHERE (( EXISTS ( SELECT  CL044006  FROM  TB044002  LEFT  OUTER  JOIN  TB044001  ON  CL043998 = CL044007  WHERE (( CL044008 = CL005500 ) AND (( CASE  WHEN (( CL044056 = 0 ) OR ( CL044056 <> CL046023 )) THEN  1  ELSE  0  END )= 1 )))) AND ( TB046336.CL008443 = CL008472 ))))))) AND (NOT ((SELECT COUNT (*) FROM TB043184 WHERE ((CL043183=CL005500) AND CL043180 IN (1))) >= 1)))


-- In Trail Blazer once the above is inserted, it would look like this:





/* The Length of the Data within a Field Having a Specific Value */


This will work on any *text field*.


-- You may be looking for all people with last names that are 10 characters:


Len(lastname) = 10


-- If you were looking for those with very long last names (often hyphenated) you could do this:

Len(lastname) > 15

-- Maybe you want to find people whose middle names are more than just an initial:


Len(middlename) > 1






Queries from the Contributions List


The following queries are entered from the Contributions list item under Contributions/Pledges:





/* Find those Donors Who have MULTIPLE Donations Awaiting a Thank You Letter */


(( SELECT  COUNT ( * ) FROM  TB010611  AS  TB047927  WHERE  TB047927.CL010595 = TB008485.CL005500  AND  TB047927.CL011184 = 0 ) > 1 ) and (CL011184= 0)


-- If you then select the NOT condition, these donors will be EXCLUDED so you may run your thank you’s on the remaining SINGLE contribution donors.




/* Find those Donors Who have a SINGLE Donation Awaiting a Thank You Letter */


(( SELECT  COUNT ( * ) FROM  TB010611  AS  TB047927  WHERE  TB047927.CL010595 = TB008485.CL005500  AND  TB047927.CL011184 = 0 )= 1 ) and (CL011184= 0)




/* Find Donors who have Multiple Contributions for a Particular Date Range */


((((TB010611.CL010568>=

-- this is the starting date (do not delete the apostrophe's):


'01/01/2012'


)) AND ((TB010611.CL010568<


-- this is one day AFTER the ending date (do not delete the apostrophe's):


'01/01/2013'


))) AND (( SELECT  COUNT ( * ) FROM  TB010611  AS  TB047927  WHERE  TB047927.CL010595 = TB008485.CL005500  AND ((( TB047927.CL010568 >=


-- the SAME starting date is repeated here (do not delete the apostrophe's):


'01/01/2012'


 ) AND ( TB047927.CL010568 <


-- the same ending date is repeated here (do not delete the apostrophe's):


'01/01/2013'


 ))))> 1 ))


-- Modify the dates in yellow above to meet your query needs.






Queries from the Addresses list


The following queries are entered from the Addresses list:





/* 9-digit Zip Codes Missing the Dash (-) Symbol */


Len(TB047772.CL044709)= 9 and (TB047772.CL044709) not like '%-%'


-- Be sure to run this from the ADDRESSES list query panel.  It will find any address records that are the full 9 digit zip, but missing the separating dash (-).



Take a look at the related resources below for links to other useful articles and videos.














Related Resources


Article: Save and Load a Search Query as a (Dynamic) Search Favorite

Article: Creating and Saving Default Formats for Reporting with the Grid

Article: SQL Wildcards

Article: How do I find a record where I am unsure of the spelling?

Article: Building a Search Query for ‘First Time Donors’ THIS Year – Save and Load it as a Favorite & Add it to your Dashboard

Article: How to Use the New Dashboard Features – Adding Graphs and Metrics with Drag-and-Drop

Article: How to Create a Donor LYBUNT Search Query (Donors Who Gave Last Year But Unfortunately Not This Year) and How to Save and Load it as a Favorite

Article: How to Delete or Rename a Saved Search Favorite (Query)

Article: Search Query – New Donors This Month, and This Year (Primarily for Nonprofits)

Article: Query and Report for Monthly Recurring Donations – Nonprofit Only

Article: How to Run a Household Export of Contact (Voter/Donor) Data to a .CSV Spreadsheet

Article: Querying by Email

Video: Filter Using New Date Controls

Video: Reporting 102 – Quick Reports and Export

Video: Favorites Deleting Saved Queries

Video: Search Tool Strip Enhancements




Trail Blazer Live Support


http://www.somebodysattic.org/images/phone%20icon_lower%20rez.jpg  Phone:  1-866-909-8700

http://sisyphus-js.herokuapp.com/assets/email_icon-0f32f4da45c2822cbb02fb36f9b9429e.png   Email:  support@trailblz.com

   Facebook: https://www.facebook.com/pages/Trail-Blazer-Software/64872951180

   Twitter: https://twitter.com/trailblazersoft



* As a policy we require that you have taken our intro training class before calling or emailing our live support team.


Click here to view our calendar for upcoming classes and events.  Feel free to sign up other members on your team for the same training.


* After registering you’ll receive a confirmation email with the instructions for how to log into the GoToMeeting session where we host our live interactive trainings.


* This service is included in your contract.



 

Are you ready to learn more? Contact Us