Community
Showing results for 
Search instead for 
Do you mean 
Reply

Establishing Cleared Activities from Tables

Accepted Solution Solved
New Member
Posts: 6
Country: United_Kingdom
Accepted Solution

Establishing Cleared Activities from Tables

I'm trying to report activities that were scheduled for completeion but have not been compled (cleared).

 

Does anyone know how to identify from the tables whether an Activity is Cleared and the details of how it was cleared.  I can see no reference to it in TBL_Activity but in TBL_History, can see Accessor_Activity_ClearedID and wonder if thats something to do with it.

 

Any guidance would be much appreciated.

 

Thanks,

 

Chris.


Accepted Solutions
Solution
Accepted by topic author ChrisWoods
‎09-25-2015 03:20 AM
Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Establishing Cleared Activities from Tables

A simple Left Outer Join would do the trick:

 

SELECT    *
FROM         TBL_ACTIVITY AS A left outer join
                      TBL_ACCESSOR_ACTIVITY_CLEARED AS AAC ON A.ACTIVITYID = AAC.ACTIVITYID
WHERE aac.ACTIVITYID is null

 

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog

View solution in original post


All Replies
Employee
Posts: 1,163
Country: USA

Re: Establishing Cleared Activities from Tables

Hello Chris, 

 

TBL_ACCESSOR_ACTIVITY_CLEARED stores activities in the database that have been cleared, the date they've been cleared and the user who did it.

 

However, I think this information may be easier to get from the OLEDB reporting provider since it includes  END_DATE_TIME and CLEARED columns that you can use to determine if it's cleared or not and if the end time should have been before today or whenever you want.

Matthew Wood
Act! SDK Support
Community Moderator
Solution
Accepted by topic author ChrisWoods
‎09-25-2015 03:20 AM
Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Establishing Cleared Activities from Tables

A simple Left Outer Join would do the trick:

 

SELECT    *
FROM         TBL_ACTIVITY AS A left outer join
                      TBL_ACCESSOR_ACTIVITY_CLEARED AS AAC ON A.ACTIVITYID = AAC.ACTIVITYID
WHERE aac.ACTIVITYID is null

 

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Nickel Contributor
Posts: 175
Country: USA

Re: Establishing Cleared Activities from Tables

Just an extension on vivek's answer, in regards to the use of * in SQL statements in general. It's advised to not use them if you can help it, since you'll probably be asking for more information than you'll need or want, since * selects all columns. If you can, actually list out the column names you need. This way, less information is being transferred, so it should be quicker for SQL Server to process and it will be a smaller result set to send across any network.

People may already know this, but I've seen a number of "I'm new to ACT" posts recently, and this could help them squeeze a little more performance out of their code.
Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Establishing Cleared Activities from Tables

Hehe good point, you caught me being lazy and not asking what fields the OP needed! *blush*
Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
New Member
Posts: 6
Country: United_Kingdom

Re: Establishing Cleared Activities from Tables

Thanks Vivek, Knif and Matthew
Copper Contributor
Posts: 70
Country: USA

Re: Establishing Cleared Activities from Tables

SELECT TBL_CONTACT_ACTIVITY.CONTACTID, TBL_ACTIVITY.*
FROM (TBL_CONTACT_ACTIVITY INNER JOIN TBL_ACTIVITY ON TBL_CONTACT_ACTIVITY.ACTIVITYID = TBL_ACTIVITY.ACTIVITYID) LEFT JOIN TBL_ACCESSOR_ACTIVITY_CLEARED ON TBL_ACTIVITY.ACTIVITYID = TBL_ACCESSOR_ACTIVITY_CLEARED.ACTIVITYID
WHERE (((TBL_CONTACT_ACTIVITY.CONTACTID)="CONTACTS'S GUID") AND ((TBL_ACCESSOR_ACTIVITY_CLEARED.ACTIVITYID) Is Null));