Trail Blazer Knowledge Base:


Home : Queries : Advanced Queries - the SQL Tab

Knowledge Base







User:

Password:



Article ID: KB169
Keyword Name: Function Button, Blue, Tool Strip, Querying, Targeting, Locating, Searching
Created: September 16, 2014
Viewed: 4486

Advanced Queries - the SQL Tab

 

Advanced Queries

 

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 


-- 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 )

FROM  TB005498 

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 )
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 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.

((((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 ))

 

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 (-).



Find our more information about Trail Blazer Campaign Services Inc. [click here]


Powered by: ClickCarts KnowledgeBase