NetSuite SYSTEM_NOTES关联SQL查询：获取指定日期历史状态

嘿，我来帮你理清楚NetSuite里SYSTEM_NOTES和业务表的关联逻辑，顺便修正你的查询问题～

首先，NetSuite的SYSTEM_NOTES表和业务表的核心关联字段是**RECORD_ID和RECORD_TYPE**：

• RECORD_ID：对应目标业务表的主键ID（比如你的CUSTOMERS表的主键，大概率是CUSTOMER_ID，如果表中主键是ID就替换成这个）
• RECORD_TYPE：标识这条系统笔记所属的记录类型，比如客户记录是customer，如果是自定义的JOB记录类型，你得确认对应的类型值（可以通过NetSuite的已保存搜索查看SYSTEM_NOTES的RECORD_TYPE选项）

你的原SQL有两个明显问题：重复的WHERE子句，以及缺失关键关联条件。下面是修正后的完整查询，我标注了核心调整点：

-- 统计2015年1月1日当天逾期且状态为未启动的JOB数量
SELECT COUNT(DISTINCT A.CUSTOMER_ID) AS job_count
FROM CUSTOMERS A
JOIN SYSTEM_NOTES C 
  ON A.CUSTOMER_ID = C.RECORD_ID  -- 关联业务表主键与SYSTEM_NOTES的记录ID
  AND C.RECORD_TYPE = 'customer'  -- 匹配记录类型，根据实际JOB类型调整
WHERE 
  -- 筛选每条记录在2015-01-01前最后一次修改STATUS的系统笔记
  C.DATE = (
    SELECT MAX(C1.DATE) 
    FROM SYSTEM_NOTES C1 
    WHERE C1.RECORD_ID = C.RECORD_ID 
      AND C1.RECORD_TYPE = C.RECORD_TYPE
      AND C1.STANDARD_FIELD = 'STATUS'
      AND C1.DATE <= '2015-01-01'
  )
  AND A.CATEGORY_0 = 'JOB'
  AND C.STANDARD_FIELD = 'STATUS'
  AND C.VALUE_NEW = 'Not Started'
  AND A.DUE_DATE < '2015-01-01'

关键调整说明： #

1. 关联逻辑补全：通过RECORD_ID匹配业务记录，RECORD_TYPE确保只关联对应类型的系统笔记，避免跨类型数据干扰
2. 子查询优化：子查询中增加了RECORD_TYPE和STANDARD_FIELD的过滤，只聚焦于STATUS字段的修改记录，提升查询准确性
3. 语法修正：合并了重复的WHERE子句，原写法会直接触发SQL语法错误
4. 统计准确性：用COUNT(DISTINCT A.CUSTOMER_ID)确保每个JOB只被统计一次，避免同一条记录的多条系统笔记导致重复计数

额外注意事项： #

• 如果你的JOB对应的不是customer类型，一定要替换RECORD_TYPE的取值，比如自定义记录类型可能是customrecord_job，可以通过NetSuite后台的已保存搜索查看对应值
• 确认CUSTOMERS表的主键确实是CUSTOMER_ID，如果主键是ID，就把关联条件改成A.ID = C.RECORD_ID
• 检查SYSTEM_NOTES中的STANDARD_FIELD是否确实是STATUS，如果是自定义状态字段，字段标识可能是custentity_xxx这类格式，需要核对系统笔记里的实际字段名

内容的提问来源于stack exchange，提问作者Maria