You need to join(not sql join) data from history tables (completed assignments) and assignment tables (worklist and workbasket). But still you will need a way to store the original workbasket since assigned operator changes from workbasket to operator once it is moved to worklist.

Other and better approach would be to store this reporting data separately whenever a new assignment is created. This would avoid collecting this data from various table and will also avoid unnecessary customizations.

With your basic requirements you can just write CreateDateTime and Workbasket Name and workobject ID (more.. if you need anything else).

The Assignment Tables (PC_ASSIGN_WORKLIST and PC_ASSIGN_WORKBASKET) are constantly changing and I believe the records are inserted and deleted depending on the state of the WorkObject i.e as soon as the WO belongs to a particular WB, a record is inserted in the PC_ASSIGN_WORKBASKET and as soon as this WO moves into someone's Worklist, that record is deleted from PC_ASSIGN_WORKBASKET and a new record is inserted in PC_ASSIGN_WORKLIST. And as soon as the WO is resolved, that record is deleted from  PC_ASSIGN_WORKLIST.  

Since the two tables are dynamic, I will not be able to find out how many WorkObjects were routed to EACH of the individual WorkBaskets in the first place.

I am using this query ONLY on the pc_history_work table.

select count(distinct pxhistoryforreference), pyassignedto from pc_history_work

Hi Avir,

This query from history table provides data about the assignments routed to work list also. If you add that condition this query provides required data.

Thanks,

VK