Finding Active Items Owned by Deleted Users Idea

0
The SBM (and TeamTrack) documentation for "Examples of SBM Pass-Through SQL" includes an example for 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)


Unfortunately, this example doesn't take into account the Secondary Owners field. This query is usually used to find records that have been stranded when the owner(s) have been deleted. What we really would like is a query to find all the active items where the owner is deleted and the secondary owners field is empty or contains all deleted users, too. This query can't be written generically since the table's database name needs to be included in the query to leverage the TS_MULTIUSERUSAGES table for the Secondary Owners field:

@where
TS_ACTIVEINACTIVE = 0
and
(
TS_OWNER in (
select TS_ID from TS_USERS
where TS_USERS.TS_STATUS = 1
)
and USR_TBL1.TS_ID not in
(
select TS_SOURCERECORDID
from TS_MULTIUSERUSAGES as MU
join TS_FIELDS as F
on MU.TS_SOURCEFIELDID = F.TS_ID and F.TS_DBNAME = 'SECONDARYOWNER'
join TS_TABLES as T
on F.TS_TABLEID = T.TS_ID and T.TS_DBNAME='USR_TBL1'
join TS_USERS as U
on MU.TS_USERID = U.TS_ID and U.TS_STATUS = 0
)
)


Just do a search and replace on USR_TBL1 with the table name used by your process app and then past it into the "Use Advanced SQL Conditions" text box on the "Search Filter" tab of your listing report.

Here's a pure SQL command you can use on your DB backend that doesn't rely on the TS_MULTIUSERUSAGES:

select TBL1.TS_ISSUEID as [Item ID] , 
TBL1.TS_TITLE as [Title],
U1.TS_NAME as [Owner],
[Secondary Owners] = isnull(STUFF((SELECT ', ' + U.TS_NAME
FROM TEAMTRACK.TS_USERS as U
INNER JOIN TEAMTRACK.USR_TBL1 AS TBL1i
ON ',' + TBL1i.TS_SECONDARYOWNER + ',' LIKE '%,' + CONVERT(VARCHAR(12), U.TS_ID) + ',%'
WHERE TBL1i.TS_ID = TBL1.TS_ID
ORDER BY TS_NAME
FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''), '(None)'),
P.TS_NAME as [Project ID]
from TEAMTRACK.USR_TBL1 as TBL1
join TEAMTRACK.TS_USERS as U1
on U1.TS_ID = TBL1.TS_OWNER
join TEAMTRACK.TS_PROJECTS as P
on P.TS_ID = TBL1.TS_PROJECTID
where TBL1.TS_ACTIVEINACTIVE = 0
and U1.TS_STATUS = 1
and CHARINDEX('0', isnull((SELECT CONVERT(VARCHAR(1), U.TS_STATUS)
FROM TEAMTRACK.TS_USERS as U
INNER JOIN TEAMTRACK.USR_TBL1 AS TBL1i
ON ',' + TBL1i.TS_SECONDARYOWNER + ',' LIKE '%,' + CONVERT(VARCHAR(12), U.TS_ID) + ',%'
WHERE TBL1i.TS_ID = TBL1.TS_ID
ORDER BY TS_STATUS
FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), '1')) = 0
order by TBL1.TS_ISSUEID


Note that this will probably only work on Microsoft SQL Server databases as written. Again, do a search and replace on "USR_TBL1" with your process application's primary table DB name.
Like
Responses (1)
  • Accepted Answer

    Saturday, December 31 2016, 09:43 PM - #Permalink
    0
    Nice.

    The requirement for all Secondary Owners being deleted makes this solution more "interesting". SQL Server's "For XML Path" is a very handy and non-portable trick that can be used for many purposes.
    Like
    • Neil Gill
      more than a month ago
      Paul,

      Do you know whether 'For XML Path' can be used in the construction of Advanced SQL Conditions on the creation of a report? I am trying to get a report where several rows of returned data is concatenated into 1 line.
      Using this as a SQL query gives me the correct result (apart from a couple of extra commas):
      SELECT [TS_ICD] + '' AS 'data()'

      FROM [SBMTest].[dbo].[U_PRODUCT_DESIGNATION]
      where TS_TITLE in ('10B1', '10B2')
      FOR XML PATH('')

      cheers
      Neil
    The reply is currently minimized Show
Your Reply

Recent Tweets

Twitter response: "Invalid or expired token."