Examples of SBM Pass-Through SQL

Example of finding items that are active and owned by deleted users:

@WHERE TS_ACTIVEINACTIVE=0 and TS_OWNER in (select TS_ID from TS_USERS 
where TS_USERS.TS_STATUS=1)

Example of finding items that are active and owned by users of a particular group called "Management:"

@WHERE TS_ACTIVEINACTIVE=0 and TS_OWNER in (select TS_MEMBERS.TS_USERID
from TS_MEMBERS, TS_GROUPS where TS_MEMBERS.TS_GROUPID = 
TS_GROUPS.TS_ID and TS_GROUPS.TS_NAME ='group name')

Example for finding incidents submitted by the users of a particular group:

@WHERE tablename.TS_SUBMITTER IN (SELECT TS_ID FROM TS_USERS WHERE
TS_USERS.TS_ID IN (SELECT TS_USERID FROM TS_MEMBERS WHERE
TS_MEMBERS.TS_GROUPID IN (SELECT TS_ID FROM TS_GROUPS WHERE
TS_GROUPS.TS_NAME = 'Technical Support'))) 

Example of finding items that were closed within two weeks (14 days) of submittal (it is possible that these items are reopened because TS_ACTIVEINACTIVE is not part of the clause):

@WHERE ( (TS_CLOSEDATE - TS_SUBMITDATE) < (14) )

Example of finding items currently in the "New" state (the sub-query can accept one unqualified TS_PROJECTID by correlating it to the table in the main selection, and SBM usually selects the project ID when you query primary tables):

@WHERE
TS_STATE in (select TS_ID from TS_STATES where TS_NAME = 'New'
and TS_STATES.TS_PROJECTID = TS_PROJECTID)

Example of finding issues in the Issues table that have attachments:

@WHERE tablename.TS_ID IN (SELECT TS_CASEID FROM
TS_ATTACHMENTS WHERE TS_SRCTABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE
TS_DBNAME = 'tablename'))

Example for finding items you owned in the past:

@WHERE
tablename.TS_ID IN (SELECT TS_CASEID FROM TS_CHANGES WHERE TS_TABLEID IN
(SELECT TS_ID FROM TS_TABLES WHERE TS_DBNAME LIKE 'tablename' )
AND TS_FLDID IN (SELECT TS_ID FROM TS_FIELDS WHERE TS_SYSCODE = 12 AND
TS_TABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE TS_DBNAME LIKE
'tablename' ) ) AND TS_NEWINT IN (SELECT TS_ID FROM TS_USERS
WHERE TS_NAME LIKE 'username' ) )

You may want to append a date range on this report.

@WHERE tablename.TS_ID IN (SELECT TS_CASEID FROM TS_CHANGES WHERE
TS_TABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE TS_DBNAME LIKE
'tablename' ) AND TS_FLDID IN (SELECT TS_ID FROM TS_FIELDS WHERE
TS_SYSCODE = 12 AND TS_TABLEID IN (SELECT TS_ID FROM TS_TABLES WHERE
TS_DBNAME LIKE 'tablename' )) AND TS_NEWINT IN (SELECT TS_ID FROM
TS_USERS WHERE TS_NAME LIKE 'username' ) AND TS_TIME >
lowerdaterange AND TS_TIME < upperdaterange )

Example for finding items in which a User field recently changed to a particular user:

@WHERE
tablename.TS_ID IN (SELECT tablename.TS_ID FROM tablename, TS_CHANGES,
TS_FIELDS, TS_TABLES, TS_USERS WHERE tablename.TS_ID = TS_CHANGES.TS_CASEID
AND TS_FIELDS.TS_NAME LIKE 'fieldname' AND TS_CHANGES.TS_FLDID =
TS_FIELDS.TS_ID AND TS_TABLES.TS_DBNAME LIKE 'tablename' AND
TS_CHANGES.TS_TABLEID =TS_TABLES.TS_ID AND TS_USERS.TS_LOGINID LIKE
'username' AND TS_CHANGES.TS_NEWINT=TS_USERS.TS_ID AND
TS_CHANGES.TS_TIME > date)

Example of using a sub-query to obtain information from Relational fields selected for the report:

@where TS_CONTACTID in (select TS_ID from TS_CONTACTS where
TS_CONTACTS.TS_STATE = 'CO')