WHERE
    a.parent_id = 391
    and a.child_id = 481
    and cm.customer_id <> -1
    and a.activity_date >= trunc(sysdate)-15
    and cm.current_flag = 'Y'
    and cases.id  = (SELECT max(cases2.id) from tallyman_mis.mis_cases cases2 WHERE cases.tallyman_case_id = cases2.tallyman_case_id) /*Only uses most current version of the case. Prevents dupes on Green_Forms_ID*/

  GROUP BY
    cm.customer_id
    ,a.activity_date
    ,a.tallyman_case_id
    ,c.anz_id
    ,cm.is_lead_customer
    ,u.forename
    ,u.Email_Address
    ,a.customer_id
    ,cases.green_forms_id
    ,cases.case_name

  ) t1

  LEFT JOIN
    (
    SELECT
      a.id Activity_ID
      ,a.activity_date Full_Date
      ,a.tallyman_case_id Case_ID
      ,cd.setcode Set_Code
      ,cd.branchcd Branch
      ,c.anz_id RM
      ,u.Email_Address Case_Manager_Email
      ,a.customer_id
      ,c.anz_id

    FROM
      Tallyman_mis.mis_activities a
      LEFT JOIN tallyman_mis.col_details cd
        ON cd.accounts1 = a.customer_id
      LEFT JOIN tallyman_mis.mis_customers c /*Get all account numbers in case*/
        ON c.id = a.customer_id
      LEFT JOIN tallyman_mis.mis_users u
        on u.id = a.activity_user

    WHERE
      a.parent_id in 1
      and a.child_id = 83 /*Set change activity*/
      and a.activity_date >= trunc(sysdate)-15
      and a.tallyman_case_id <> -1
      and c.anz_id is not null
      and cd.branchcd is not null
    )f1
    ON f1.Full_Date = t1.Full_Date
      and f1.Case_ID = t1.Case_ID
      and f1.RM = t1.RM

WHERE
  t1.case_id is not null

GROUP BY
  t1.Full_Date
  ,t1.case_name
  ,t1.Case_ID
  ,t1.RM
  ,t1.is_lead_customer
  ,f1.Set_Code
  ,f1.Branch
  ,t1.Case_Manager
  ,t1.Case_Manager_Email
  ,t1.green_forms_id
  ,t1.Written_Off_Arrears
  ,t1.Provision_Account

ORDER BY
  t1.Case_ID
  ,t1.is_lead_customer desc