Actual vs Target

view name = 'actual_vs_target'

[NEW] Query

 WITH target_view AS (
         WITH daterange AS (
                 SELECT generate_series(target.start_date::timestamp with time zone, target.end_date::timestamp with time zone, '1 day'::interval)::date AS date,
                    target.user_id,
                    target.branch,
                    target.division,
                    target."position",
                    target.pic,
                    target.target_inbound,
                    target.target_leads,
                    target.target_mql,
                    target."target_mqlPlus",
                    target."target_tourPlus",
                    target."target_trialPlus",
                    target.target_convert,
                    target.end_date - target.start_date + 1 AS total_days
                   FROM target
                ), dailytargets AS (
                 SELECT daterange.date,
                    daterange.branch,
                    daterange.division,
                    daterange."position",
                    daterange.pic,
                    daterange.target_inbound::double precision / daterange.total_days::double precision AS daily_target_inbound,
                    daterange.target_leads::double precision / daterange.total_days::double precision AS daily_target_leads,
                    daterange.target_mql::double precision / daterange.total_days::double precision AS daily_target_mql,
                    daterange."target_mqlPlus"::double precision / daterange.total_days::double precision AS daily_target_mqlplus,
                    daterange."target_tourPlus"::double precision / daterange.total_days::double precision AS daily_target_tourplus,
                    daterange."target_trialPlus"::double precision / daterange.total_days::double precision AS daily_target_trialplus,
                    daterange.target_convert::double precision / daterange.total_days::double precision AS daily_target_convert
                   FROM daterange
                )
         SELECT dailytargets.date,
            dailytargets.branch,
            dailytargets.division,
            dailytargets."position",
            dailytargets.pic,
            dailytargets.daily_target_inbound AS target_inbound,
            dailytargets.daily_target_leads AS target_leads,
            dailytargets.daily_target_mql AS target_mql,
            dailytargets.daily_target_mqlplus AS "target_mqlPlus",
            dailytargets.daily_target_tourplus AS "target_tourPlus",
            dailytargets.daily_target_trialplus AS "target_trialPlus",
            dailytargets.daily_target_convert AS target_convert
           FROM dailytargets
          GROUP BY dailytargets.date, dailytargets.branch, dailytargets.division, dailytargets."position", dailytargets.pic, dailytargets.daily_target_inbound, dailytargets.daily_target_leads, dailytargets.daily_target_mql, dailytargets.daily_target_mqlplus, dailytargets.daily_target_tourplus, dailytargets.daily_target_trialplus, dailytargets.daily_target_convert
        )
 SELECT date,
    branch,
    COALESCE(division, NULL::text) AS division,
    COALESCE("position", NULL::text) AS "position",
    pic,
    sum(inbound) AS inbound,
    sum(target_inbound) AS target_inbound,
    sum(leads) AS leads,
    sum(target_leads) AS target_leads,
    sum(mql) AS mql,
    sum(target_mql) AS target_mql,
    sum(appt_mql) AS appt_mql,
    sum(appt) AS appt,
    sum(mqlplus) AS mqlplus,
    sum("target_mqlPlus") AS "target_mqlPlus",
    sum(appt_mqlplus) AS appt_mqlplus,
    sum(tourplus) AS tourplus,
    sum("target_tourPlus") AS "target_tourPlus",
    sum(appt_tourplus) AS appt_tourplus,
    sum(trialplus) AS trialplus,
    sum("target_trialPlus") AS "target_trialPlus",
    sum(appt_trialplus) AS appt_trialplus,
    sum(convert) AS convert,
    sum(target_convert) AS target_convert,
    (to_char(date_trunc('week'::text, date::timestamp with time zone), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, date::timestamp with time zone) + '6 days'::interval, 'DD Mon'::text) AS week
   FROM ( SELECT target_view.date,
            target_view.branch,
            target_view.division,
            target_view."position",
            target_view.pic,
            0 AS inbound,
            target_view.target_inbound,
            0 AS leads,
            target_view.target_leads,
            0 AS mql,
            target_view.target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            target_view."target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            target_view."target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            target_view."target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            target_view.target_convert
           FROM target_view
        UNION ALL
         SELECT tac_inbound.date,
            tac_inbound.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_inbound.contact_owner AS pic,
            tac_inbound.inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_inbound
        UNION ALL
         SELECT tac_leads.date,
            tac_leads.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_leads.contact_owner AS pic,
            0 AS inbound,
            0 AS target_inbound,
            tac_leads.leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_leads
        UNION ALL
         SELECT tac_mql.date,
            tac_mql.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_mql.last_created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            tac_mql.mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_mql
        UNION ALL
         SELECT tac_appt_mql.date,
            tac_appt_mql.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_appt_mql.created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            tac_appt_mql.mql AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_appt_mql
        UNION ALL
         SELECT tac_appt_appt.date,
            tac_appt_appt.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_appt_appt.created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            tac_appt_appt.appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_appt_appt
        UNION ALL
         SELECT tac_appt_mqlplus.date,
            tac_appt_mqlplus.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_appt_mqlplus.created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            tac_appt_mqlplus.appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_appt_mqlplus
        UNION ALL
         SELECT "tac_tourPlus".date,
            "tac_tourPlus".branch,
            NULL::text AS division,
            NULL::text AS "position",
            "tac_tourPlus".last_created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            "tac_tourPlus".tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM "tac_tourPlus"
        UNION ALL
         SELECT tac_appt_tourplus.date,
            tac_appt_tourplus.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_appt_tourplus.created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            tac_appt_tourplus.appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_appt_tourplus
        UNION ALL
         SELECT "tac_trialPlus".date,
            "tac_trialPlus".branch,
            NULL::text AS division,
            NULL::text AS "position",
            "tac_trialPlus".last_created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            "tac_trialPlus".trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM "tac_trialPlus"
        UNION ALL
         SELECT tac_appt_trialplus.date,
            tac_appt_trialplus.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_appt_trialplus.created_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            tac_appt_trialplus.appt_trialplus,
            0 AS convert,
            0 AS target_convert
           FROM tac_appt_trialplus
        UNION ALL
         SELECT tac_convert.date,
            tac_convert.branch,
            NULL::text AS division,
            NULL::text AS "position",
            tac_convert.convert_by AS pic,
            0 AS inbound,
            0 AS target_inbound,
            0 AS leads,
            0 AS target_leads,
            0 AS mql,
            0 AS target_mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS "target_mqlPlus",
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS "target_tourPlus",
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS "target_trialPlus",
            0 AS appt_trialplus,
            tac_convert.convert,
            0 AS target_convert
           FROM tac_convert) combined_data
  WHERE date IS NOT NULL AND pic IS NOT NULL
  GROUP BY date, division, "position", branch, pic
  ORDER BY date, branch;