01-24-2013 07:19 AM
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.
01-25-2013 06:03 AM
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
01-24-2013 08:51 AM
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.
01-25-2013 06:03 AM
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
01-25-2013 06:25 AM
01-25-2013 06:39 AM
01-28-2013 01:53 AM
04-30-2015 01:02 PM