Ticket Status

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE reporting_tz STRING DEFAULT 'America/New_York';  -- support team reporting time zone
DECLARE end_date DATE DEFAULT CURRENT_DATE(reporting_tz);
DECLARE start_date DATE DEFAULT DATE_SUB(end_date, INTERVAL 90 DAY);

WITH valid_tickets AS (
  -- Spam, deleted and merged tickets excluded (a merged source ticket never counts as solved)
  SELECT ticket_id
  FROM `your-project.crm.support_tickets`
  WHERE COALESCE(is_spam, FALSE) = FALSE
    AND COALESCE(is_deleted, FALSE) = FALSE
    AND merged_into_ticket_id IS NULL
),
status_changes AS (
  -- Audit-log status changes mapped in one lookup onto the governed set; custom labels roll up to their parent category
  SELECT a.ticket_id, a.changed_at, m.status_category AS ticket_status,
         COALESCE(m.custom_status_label, m.status_category) AS ticket_status_detail
  FROM `your-project.crm.ticket_status_audit` a
  JOIN valid_tickets v ON v.ticket_id = a.ticket_id
  JOIN `your-project.crm.ticket_status_map` m
    ON m.source_system = a.source_system AND LOWER(m.raw_status) = LOWER(a.raw_status)
),
current_status AS (
  -- Current status: latest mapped change per ticket, no snapshot cutoff (drives backlog views)
  SELECT ticket_id, ticket_status, ticket_status_detail
  FROM status_changes
  QUALIFY ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY changed_at DESC) = 1
),
snapshots AS (
  -- One daily snapshot at 00:00 in the reporting time zone
  SELECT snapshot_date, TIMESTAMP(snapshot_date, reporting_tz) AS snapshot_ts
  FROM UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) AS snapshot_date
),
snapshot_status AS (
  -- Status held at each snapshot: last change at or before 00:00 (reopens from solved to open follow the audit log)
  SELECT s.snapshot_date, c.ticket_id, c.ticket_status, c.ticket_status_detail
  FROM snapshots s
  JOIN status_changes c ON c.changed_at <= s.snapshot_ts
  QUALIFY ROW_NUMBER() OVER (PARTITION BY s.snapshot_date, c.ticket_id ORDER BY c.changed_at DESC) = 1
)
-- Both readings, never mixed: status_basis separates current status from daily snapshot status
SELECT 'current' AS status_basis, CAST(NULL AS DATE) AS snapshot_date, ticket_id, ticket_status, ticket_status_detail
FROM current_status
UNION ALL
SELECT 'snapshot' AS status_basis, snapshot_date, ticket_id, ticket_status, ticket_status_detail
FROM snapshot_status
ORDER BY status_basis, snapshot_date, ticket_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative window and support team reporting time zone
  SELECT date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 90) AS start_date,
         to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')) AS end_date,
         'America/New_York' AS reporting_tz
),
valid_tickets AS (
  -- Spam, deleted and merged tickets excluded (a merged source ticket never counts as solved)
  SELECT ticket_id
  FROM crm_support_tickets
  WHERE COALESCE(is_spam, FALSE) = FALSE
    AND COALESCE(is_deleted, FALSE) = FALSE
    AND merged_into_ticket_id IS NULL
),
status_changes AS (
  -- Audit-log status changes mapped in one lookup onto the governed set; custom labels roll up to their parent category
  SELECT a.ticket_id, a.changed_at, m.status_category AS ticket_status,
         COALESCE(m.custom_status_label, m.status_category) AS ticket_status_detail
  FROM crm_ticket_status_audit a
  JOIN valid_tickets v ON v.ticket_id = a.ticket_id
  JOIN crm_ticket_status_map m
    ON m.source_system = a.source_system AND LOWER(m.raw_status) = LOWER(a.raw_status)
),
current_status AS (
  -- Current status: latest mapped change per ticket, no snapshot cutoff (drives backlog views)
  SELECT ticket_id, ticket_status, ticket_status_detail
  FROM (
    SELECT ticket_id, ticket_status, ticket_status_detail,
           ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY changed_at DESC) AS rn
    FROM status_changes
  ) x
  WHERE rn = 1
),
snapshots AS (
  -- One daily snapshot at 00:00 in the reporting time zone
  SELECT snapshot_date, to_utc_timestamp(CAST(snapshot_date AS TIMESTAMP), p.reporting_tz) AS snapshot_ts
  FROM params p
  LATERAL VIEW explode(sequence(p.start_date, p.end_date)) d AS snapshot_date
),
snapshot_status AS (
  -- Status held at each snapshot: last change at or before 00:00 (reopens from solved to open follow the audit log)
  SELECT snapshot_date, ticket_id, ticket_status, ticket_status_detail
  FROM (
    SELECT s.snapshot_date, c.ticket_id, c.ticket_status, c.ticket_status_detail,
           ROW_NUMBER() OVER (PARTITION BY s.snapshot_date, c.ticket_id ORDER BY c.changed_at DESC) AS rn
    FROM snapshots s
    JOIN status_changes c ON c.changed_at <= s.snapshot_ts
  ) y
  WHERE rn = 1
)
-- Both readings, never mixed: status_basis separates current status from daily snapshot status
SELECT 'current' AS status_basis, CAST(NULL AS DATE) AS snapshot_date, ticket_id, ticket_status, ticket_status_detail
FROM current_status
UNION ALL
SELECT 'snapshot' AS status_basis, snapshot_date, ticket_id, ticket_status, ticket_status_detail
FROM snapshot_status
ORDER BY status_basis, snapshot_date, ticket_id;