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.
Tip: 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
Phone: 1-866-909-8700
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.
