Discussions  >  CollabNet TeamForge  >  SQL Query for Artifact Current Assigned To Value

Back to topic list

SQL Query for Artifact Current Assigned To Value

Author michael_alexander
Full name Michael Alexander
Date 2018-03-23 06:00:30 PDT
Message We are running TeamForge Version 17.8.655.

We do external reporting against TeamForge data using SQL Queries.

I would like to know the recommended query to return an artifact's current Assigned_To value.

We are using a query we developed for this, but this query may not be the best approach as it seems to be a very time-consuming query -

select
  a.id as ARTIFACT,
  i.title as TITLE,
  upper(ud.username) as ASSIGNED_TO_USER,
  ud.full_name as "ASSIGNED_TO"
from
    artifact a
      left join artifact_dimension ad on ad.id = a.id
      left join artifact_transaction_fact atf on ad.artifact_key=atf.​artifact_key
      left join user_dimension ud on ud.user_key=atf.assi​gned_to_key,
    item i
where
    a.id = i.id
    and i.date_created > '01-MAR-18'
    and atf.EFFECTIVE_TILL in (select max(atf1.EFFECTIVE_TILL) from artifact_transaction_fact atf1
          where atf.assigned_to_key=​atf1.assigned_to_key​ )
order by ud.full_name

Is there a more efficient way to retrieve the Assigned_To for an artifact with a SQL Query?
Attachments

« Previous message in topic | 1 of 5 | Next message in topic »

Messages

Show all messages in topic

SQL Query for Artifact Current Assigned To Value michael_alexander Michael Alexander 2018-03-23 06:00:30 PDT
     Re: SQL Query for Artifact Current Assigned To Value markphip Mark Phippard 2018-03-23 06:39:59 PDT
     RE: SQL Query for Artifact Current Assigned To Value michael_alexander Michael Alexander 2018-03-23 07:35:23 PDT
         Re: SQL Query for Artifact Current Assigned To Value markphip Mark Phippard 2018-03-23 07:52:02 PDT
     RE: SQL Query for Artifact Current Assigned To Value michael_alexander Michael Alexander 2018-03-23 09:09:11 PDT
Messages per page: