# Combined

{% code title="combined" overflow="wrap" lineNumbers="true" %}

```plsql
 WITH rankedtempleads AS (
         SELECT t.id,
            t."timestamp",
            t.branch,
            t.name,
            t.phone,
            t.email,
            t.dob,
            t.source,
            t.sremarks,
            t.medium,
            t.mremarks,
            t.content,
            t.pic,
            t.referrer,
            t."currentPath",
            t."agree_privacyPolicy",
            t.agree_newsletter,
            t.gclid,
            t.google_key,
            t.ttclid,
            t.form_id,
            t.gcampaign_id,
            t.gadgroup_id,
            t.gcreative_id,
            t.category_program,
            t.trial_day,
            t.free_trial,
            t.fbclid,
            t.fadset_id,
            t.fcampaign_id,
            t.fadset_name,
            t.fcampaign_name,
            t.tour_date,
            t.tour_time,
            t."dup?",
            t.contact_owner,
            t.sleekflow_id,
            t."conversationId",
            t.leads_qualification,
            t.interest,
            t.interest_remarks,
            t.offer_trial,
            t.reply_after_offer,
            t.sremarks_id,
            t.uid,
            t."assigned?",
            t.assigned_date,
            t.member_id,
            t.gender,
            t.hide,
            t.leads_notes,
            t.family_id,
            row_number() OVER (PARTITION BY t.phone ORDER BY t."timestamp") AS row_num
           FROM temp_leads t
          WHERE NOT (EXISTS ( SELECT 1
                   FROM leads l
                  WHERE l.phone = t.phone)) AND NOT (EXISTS ( SELECT 1
                   FROM family f
                  WHERE f.family_phone = t.phone)) AND t.phone IS NOT NULL
        ), combined AS (
         SELECT leads.uid,
            leads."timestamp",
            leads.phone,
            leads.email,
            leads.name,
            leads.gender,
            leads.date_of_birth,
            leads.age,
            leads.age_group,
            leads.branch,
            leads.language,
            leads.contact_owner,
            leads.source,
            leads.sremarks,
            leads.content,
            leads.medium,
            leads.mremarks,
            leads.assigned_pic,
            leads.interest,
            leads.leads_notes,
            leads.user_type,
            leads.kyzn_account,
            leads.sleekflow_id,
            leads.leads_date,
            leads.mql_date,
            leads."mqlPlus_date",
            leads."tourPlus_date",
            leads.convert_date,
            leads.loss_date,
            leads.tour_date,
            leads."is_tour_show?",
            leads.trial_date,
            leads."is_trial_show?",
            leads."is_inbound?",
            leads."is_leads?",
            leads."is_mql?",
            leads."is_mqlPlus?",
            leads."is_tourPlus?",
            leads."is_trialPlus?",
            leads."is_convert?",
            leads.interest_remarks,
            leads.submitted_email,
            leads.assigned_email,
            leads."trialPlus_date",
            leads.membership_name,
            leads.package_name,
            leads.start_membership,
            leads.member_id,
            leads.total_amount,
            leads.payment_method,
            leads.promotion,
            leads.ref_program,
            leads.ref_id,
            leads.ref_name,
            leads.ref_phone,
            leads.identity_photo,
            leads.status,
            leads.convert_by,
            leads.end_membership,
            leads.signature,
            leads.contract,
            leads.offer_trial,
            leads.reply_after_offer,
            leads.last_appt_category,
            leads."conversationId",
            leads.assigned_date,
            leads.last_created_by,
            leads.is_renewal,
            leads.approach_by,
            leads.app_status,
            leads.start_approach,
            leads.expired_approach,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END AS appt_date,
            (to_char(date_trunc('week'::text, leads."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            (to_char(date_trunc('week'::text, leads.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql,
            (to_char(date_trunc('week'::text, leads."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus,
            (to_char(date_trunc('week'::text,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date,
            (to_char(date_trunc('week'::text, leads.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert,
            NULL::text AS family_id,
            leads.hide,
            leads.id,
            NULL::text AS family_relationship,
            leads.renewal_category,
            leads.convert_category,
            leads.is_package
           FROM leads
          WHERE leads.hide IS NULL OR leads.hide = false
        UNION ALL
         SELECT family.uid,
            family.created_date AS "timestamp",
            family.family_phone AS phone,
            family.email,
            family.family_name AS name,
            family.gender,
            family.date_of_birth,
            family.age,
            family.age_group,
            family.branch,
            NULL::text AS language,
            family.contact_owner,
            family.source,
            family.sremarks,
            NULL::text AS content,
            family.medium,
            family.mremarks,
            family.assigned_pic,
            NULL::text AS interest,
            NULL::text AS leads_notes,
            NULL::text AS user_type,
            family.kyzn_account,
            NULL::text AS sleekflow_id,
            family.leads_date,
            family.mql_date,
            family."mqlPlus_date",
            family."tourPlus_date",
            family.convert_date,
            NULL::timestamp without time zone AS loss_date,
            NULL::timestamp without time zone AS tour_date,
            NULL::boolean AS "is_tour_show?",
            family.trial_date,
            family."is_trial_show?",
            family."is_inbound?",
            family."is_leads?",
            family."is_mql?",
            family."is_mqlPlus?",
            family."is_tourPlus?",
            family."is_trialPlus?",
            family."is_convert?",
            NULL::text AS interest_remarks,
            NULL::text AS submitted_email,
            NULL::text AS assigned_email,
            family."trialPlus_date",
            family.membership_name,
            family.package_name,
            family.start_membership,
            family.member_id,
            family.total_amount,
            family.payment_method,
            family.promotion,
            NULL::text AS ref_program,
            NULL::text AS ref_id,
            NULL::text AS ref_name,
            family.family_phone AS ref_phone,
            NULL::text AS identity_photo,
            family.status,
            family.convert_by,
            family.end_membership,
            NULL::text AS signature,
            NULL::text AS contract,
            NULL::boolean AS offer_trial,
            NULL::boolean AS reply_after_offer,
            NULL::text AS last_appt_category,
            NULL::text AS "conversationId",
            family.assigned_date,
            NULL::text AS last_created_by,
            family.is_renewal,
            family.approach_by,
            NULL::text AS app_status,
            family.start_approach,
            family.expired_approach,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END AS appt_date,
            (to_char(date_trunc('week'::text, family.created_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.created_date) + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            (to_char(date_trunc('week'::text, family.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql,
            (to_char(date_trunc('week'::text, family."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus,
            (to_char(date_trunc('week'::text,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date,
            (to_char(date_trunc('week'::text, family.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert,
            family.family_id,
            family.hide,
            family.id,
            family.family_relationship,
            family.renewal_category,
            family.convert_category,
            family.is_package
           FROM family
          WHERE family.hide IS NULL OR family.hide = false
        UNION ALL
         SELECT t.uid,
            t."timestamp",
            t.phone,
            t.email,
            t.name,
            NULL::text AS gender,
            t.dob AS date_of_birth,
            NULL::text AS age,
            NULL::text AS age_group,
            t.branch,
            NULL::text AS language,
            t.contact_owner,
            t.source,
            t.sremarks,
            t.content,
            t.medium,
            t.mremarks,
            t.pic AS assigned_pic,
            t.interest,
            t.free_trial AS leads_notes,
            NULL::text AS user_type,
            NULL::text AS kyzn_account,
            t.sleekflow_id,
            NULL::timestamp without time zone AS leads_date,
            NULL::timestamp without time zone AS mql_date,
            NULL::timestamp without time zone AS "mqlPlus_date",
            NULL::timestamp without time zone AS "tourPlus_date",
            NULL::timestamp without time zone AS convert_date,
            NULL::timestamp without time zone AS loss_date,
            NULL::timestamp without time zone AS tour_date,
            NULL::boolean AS "is_tour_show?",
            NULL::timestamp without time zone AS trial_date,
            NULL::boolean AS "is_trial_show?",
            NULL::boolean AS "is_inbound?",
            NULL::boolean AS "is_leads?",
            NULL::boolean AS "is_mql?",
            NULL::boolean AS "is_mqlPlus?",
            NULL::boolean AS "is_tourPlus?",
            NULL::boolean AS "is_trialPlus?",
            NULL::boolean AS "is_convert?",
            t.interest_remarks,
            NULL::text AS submitted_email,
            NULL::text AS assigned_email,
            NULL::timestamp without time zone AS "trialPlus_date",
            NULL::text AS membership_name,
            NULL::text AS package_name,
            NULL::date AS start_membership,
            NULL::text AS member_id,
            NULL::bigint AS total_amount,
            NULL::text AS payment_method,
            NULL::text AS promotion,
            NULL::text AS ref_program,
            t.referrer AS ref_id,
            NULL::text AS ref_name,
            NULL::bigint AS ref_phone,
            NULL::text AS identity_photo,
            NULL::text AS status,
            NULL::text AS convert_by,
            NULL::date AS end_membership,
            NULL::text AS signature,
            NULL::text AS contract,
            t.offer_trial,
            t.reply_after_offer,
            NULL::text AS last_appt_category,
            t."conversationId",
            t.assigned_date,
            NULL::text AS last_created_by,
            NULL::boolean AS is_renewal,
            NULL::text AS approach_by,
            NULL::text AS app_status,
            NULL::timestamp without time zone AS start_approach,
            NULL::timestamp without time zone AS expired_approach,
            NULL::timestamp without time zone AS appt_date,
            (to_char(date_trunc('week'::text, t."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, t."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            NULL::text AS week_mql,
            NULL::text AS week_mqlplus,
            NULL::text AS week_appt_date,
            NULL::text AS week_convert,
            NULL::text AS family_id,
            t.hide,
            t.id,
            NULL::text AS family_relationship,
            NULL::text AS renewal_category,
            NULL::text AS convert_category,
            NULL::boolean AS is_package
           FROM rankedtempleads t
          WHERE t.row_num = 1
        )
 SELECT uid,
    "timestamp",
    phone,
    email,
    name,
    gender,
    date_of_birth,
    age,
    age_group,
    branch,
    language,
    contact_owner,
    source,
    sremarks,
    content,
    medium,
    mremarks,
    assigned_pic,
    interest,
    leads_notes,
    user_type,
    kyzn_account,
    sleekflow_id,
    leads_date,
    mql_date,
    "mqlPlus_date",
    "tourPlus_date",
    convert_date,
    loss_date,
    tour_date,
    "is_tour_show?",
    trial_date,
    "is_trial_show?",
    "is_inbound?",
    "is_leads?",
    "is_mql?",
    "is_mqlPlus?",
    "is_tourPlus?",
    "is_trialPlus?",
    "is_convert?",
    interest_remarks,
    submitted_email,
    assigned_email,
    "trialPlus_date",
    membership_name,
    package_name,
    start_membership,
    member_id,
    total_amount,
    payment_method,
    promotion,
    ref_program,
    ref_id,
    ref_name,
    ref_phone,
    identity_photo,
    status,
    convert_by,
    end_membership,
    signature,
    contract,
    offer_trial,
    reply_after_offer,
    last_appt_category,
    "conversationId",
    assigned_date,
    last_created_by,
    is_renewal,
    approach_by,
    app_status,
    start_approach,
    expired_approach,
    appt_date,
    week_inbound,
    week_mql,
    week_mqlplus,
    week_appt_date,
    week_convert,
    family_id,
    hide,
    id,
        CASE
            WHEN family_id IS NULL THEN uid
            ELSE family_id
        END AS cid,
    family_relationship,
    renewal_category,
    convert_category,
    is_package
   FROM combined
  WHERE phone IS NOT NULL;
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://work.bahrul.me/work/kyzn/database-management/table-view/combined.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.