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.
The following is a list of queries I have compiled over the years. They may not be in any particular order so I will endeavor to provide enough explanation that will allow them be easily searched.
In the following queries, you will just need to copy and paste the SQL query text into the SQL tab as shown in the picture above, then click your search button.
Queries from the Voters/Donors list
The following queries are entered from the Voters or Donors menu.
Donors designated as matching employee donations
(EXISTS (SELECT CL046762 FROM TB046761 WHERE ((CL046763=CL005500) )))
Voters or Donors whose records contain images
(EXISTS (SELECT CL043235 FROM TB043228 WHERE (CL043235=CL005500)))
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 having more than one address assigned
(SELECT COUNT (*) FROM tb044698 WHERE CL044743=CL005500) >= 2
Variations on voters or donors 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 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
( 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 or 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 or Donors 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 at 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 )
WHERE ((( CL005529 = 'REP' ) AND CL005500 IN
( SELECT CL005500 FROM TB005498 WHERE ( CL008443 = CL008472 ))))) >= 2
REP is republican, DEM is democrat. You may need to use the party drop down to see what other party values you would like to use.
Multi-person households where there are at LEAST two people are older than 60
The dates need to be adjusted to accomodate the day you are running this query. The date is referenced 3 times:
( SELECT COUNT ( TB005498.CL005500 )
TB005498.CL008443 = TB008485.CL008443
(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 )
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 Contribution list
The following queries are entered from the Contributions menu item under Contributions/Pledges.
Find those donors who have MULTIPLE donations awaiting a thank you.
(( 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.
(( 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.
-- this is the starting date (do not delete the apostrophe's):
)) AND ((TB010611.CL010568<
-- this is one day AFTER the ending date (do not delete the apostrophe's):
))) 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):
) AND ( TB047927.CL010568 <
-- the same ending date is repeated here (do not delete the apostrophe's):
))))> 1 ))
Query from the Address list
9-digit zip codes missing the dash (-)
Len(TB047772.CL044709)= 9 and (TB047772.CL044709) not like ‘%-%’
-- Be sure to run this from the ADDRESS list query panel. It will find any address records that are the full 9 digit zip, but missing the separating dash (-).