TAC Appt

view name = 'tac_appt'

[NEW] Query

 WITH status_calculated AS (
         SELECT date_trunc('day'::text, appt.appt_date)::date AS date,
            to_char(date_trunc('month'::text, appt.appt_date), 'YYYY Mon'::text) AS month,
            (to_char(date_trunc('week'::text, appt.appt_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, appt.appt_date) + '6 days'::interval, 'DD Mon'::text) AS week,
            appt.branch,
            appt.contact_owner,
            appt.assigned_pic,
            appt.created_by,
                CASE
                    WHEN bool_or(appt."is_tour_show?") THEN true
                    WHEN bool_or(appt."is_trial_show?") THEN true
                    WHEN bool_or(appt."is_trial_show?") IS FALSE THEN false
                    WHEN bool_or(appt."is_tour_show?") IS FALSE THEN false
                    ELSE NULL::boolean
                END AS status,
            count(appt.appt_date) AS appt
           FROM appt
          WHERE appt.hide IS NULL OR appt.hide = false
          GROUP BY (date_trunc('day'::text, appt.appt_date)::date), (to_char(date_trunc('month'::text, appt.appt_date), 'YYYY Mon'::text)), ((to_char(date_trunc('week'::text, appt.appt_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, appt.appt_date) + '6 days'::interval, 'DD Mon'::text)), appt.branch, appt.contact_owner, appt.assigned_pic, appt.created_by
        ), mql_calculated AS (
         SELECT date_trunc('day'::text, appt.mql_date)::date AS date,
            to_char(date_trunc('month'::text, appt.mql_date), 'YYYY Mon'::text) AS month,
            (to_char(date_trunc('week'::text, appt.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, appt.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week,
            appt.branch,
            appt.contact_owner,
            appt.assigned_pic,
            appt.created_by,
            count(appt.mql_date) AS mql
           FROM appt
          WHERE appt.hide IS NULL OR appt.hide = false
          GROUP BY (date_trunc('day'::text, appt.mql_date)::date), (to_char(date_trunc('month'::text, appt.mql_date), 'YYYY Mon'::text)), ((to_char(date_trunc('week'::text, appt.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, appt.mql_date) + '6 days'::interval, 'DD Mon'::text)), appt.branch, appt.contact_owner, appt.assigned_pic, appt.created_by
        )
 SELECT COALESCE(sc.date, mc.date) AS date,
    COALESCE(sc.month, mc.month) AS month,
    COALESCE(sc.week, mc.week) AS week,
    COALESCE(sc.branch, mc.branch) AS branch,
    COALESCE(sc.contact_owner, mc.contact_owner) AS contact_owner,
    COALESCE(sc.assigned_pic, mc.assigned_pic) AS assigned_pic,
    COALESCE(sc.created_by, mc.created_by) AS created_by,
    sc.status,
    COALESCE(sc.appt, 0::bigint) AS total_appt,
    COALESCE(mc.mql, 0::bigint) AS total_mql
   FROM status_calculated sc
     FULL JOIN mql_calculated mc ON sc.date = mc.date AND sc.branch = mc.branch AND sc.contact_owner = mc.contact_owner AND sc.assigned_pic = mc.assigned_pic AND sc.created_by = mc.created_by
  ORDER BY (COALESCE(sc.date, mc.date)), (COALESCE(sc.branch, mc.branch));