Establishing Cleared Activities from Tables

[ChrisWoods]

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.

[vivek · Accepted solution]

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

[mwood]

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.

[vivek · Accepted solution]

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

[knif]

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.

[vivek]

Hehe good point, you caught me being lazy and not asking what fields the OP needed! *blush*

[ChrisWoods]

Thanks Vivek, Knif and Matthew

[kritguy]

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