• Looking at the plan, 73% of the estimated cost comes from 2 joins and a 'not null' filter, which I've reconstructed as

    Select

    AUND.AUND_INBOX,

    RPT_SQL_CL.dbo.FFG_FX_JUST_DATE(A.WF_START) as Expr1007

    from

    RPT_SQL_CL.dbo.IREP_T_WF_INFO as A

    right join -- 13,988,837 rows (19% cost)

    RPT_SQL_CL.dbo.IREP_T_UNDW_STAFF

    on

    A.ASSIGNED_TO = RPT_SQL_CL.dbo.IREP_T_UNDW_STAFF.UNDS_STAFF_INBOX

    join -- 13,980,811 rows (50% cost)

    RPT_SQL_CL.dbo.IREP_T_AGENCY_UNDW as AUND

    on

    AUND.AUND_AGENCY_CODE=A.AGENCY_CODE

    where

    A.ASSIGNED_TO IS NOT NULL -- 22,079,800 rows(14% cost)

    The hash join (for the DISTINCT) adds another 14% to the cost.

    To do much further analysis really needs the original source of the view IREP_V_DAILY_COUNTS_TERRRITORY (reconstructing it from the plan takes too long!) and DDL for the tables IREP_T_WF_INFO, IREP_T_UNDW_STAFF, IREP_T_AGENCY_UNDW, IREP_T_EMPLOYEES and IREP_T_CDIR_EMPLOYEES.

    Also I'd be interested to see the source of the User-defined function FFG_FX_JUST_DATE since, although it doesn't add the the estimated cost, it appears the estimate is for it to be called over 13 million times.

    Derek