时间戳

首页 > 解决方案 > SQL 在应该拉取值时拉取 null

sql - SQL 在应该拉取值时拉取 null

问题描述

我以前有这个工作，我无法弄清楚它发生了什么，它不会显示 Q4 和 Q5 列的真实值。

此查询正在提取调查数据，并且 Q4 和 Q5 的所有行都为空。Q1-Q3 的数据正确显示。

这是我的查询：

DECLARE @start as datetime2(0) = '2021-01-01'

SELECT
    MAX(IIF(metric.name = 'Q1' AND survey.sr_actual_value BETWEEN 1 AND 5, survey.sr_actual_value, NULL)) AS Q1,
    MAX(IIF(metric.name = 'Q2' AND survey.sr_actual_value BETWEEN 1 AND 5, survey.sr_actual_value, NULL)) AS Q2,
    MAX(IIF(metric.name = 'Q3' AND survey.sr_actual_value BETWEEN 1 AND 5, survey.sr_actual_value, NULL)) AS Q3,
    MAX(IIF(metric.name = 'Q4' AND survey.sr_actual_value BETWEEN 1 AND 5, survey.sr_actual_value, NULL)) AS Q4,
    MAX(IIF(metric.name = 'Q5' AND survey.sr_actual_value BETWEEN 1 AND 5, survey.sr_actual_value, NULL)) AS Q5,
    MAX(IIF(metric.name = 'Q6', survey.sr_string_value, NULL)) AS Comment,
    MAX(survey.si_taken_on) as Taken,
    MAX(ims.number) as [Number],
    MAX(ims.sys_id) as sys_id,
    MAX(tech.name) AS Tech,
    MAX(man.first_name) AS Manager,
    MAX(cust.name) AS Customer,
    CASE
    WHEN MAX(awi.queue) = 'b63d965cdbb05454586593a6f4961968' THEN 'SS'
    WHEN MAX(awi.queue) = '67e1f6cbdb9298548fd6695e1396199c' THEN 'Chat'
    ELSE NULL
    END as [Group]

FROM [v2_sn].[u_interaction_survey_reporting] as survey

LEFT JOIN v2_sn.sys_user as cust on cust.sys_id = survey.si_user
LEFT JOIN v2_sn.asmt_metric as metric on metric.sys_id = survey.sr_metric
LEFT JOIN v2_sn.interaction as ims on ims.sys_id = survey.inter_sys_id
LEFT JOIN v2_sn.sys_user as tech on tech.sys_id = ims.assigned_to
LEFT JOIN v2_sn.sys_user as man on man.sys_id = tech.manager
left join v2_sn.awa_work_item as awi on awi.document_id = ims.sys_id

WHERE survey.si_taken_on >= @start
    and metric.name in ('Q1','Q2','Q3','Q4''Q5','Q6')
    and survey.si_metric_type = '1c4f20afdbc614103779a6dc139619c9'
    and awi.queue in ('b63d965cdbb05454586593a6f4961968', '67e1f6cbdb9298548fd6695e1396199c')

GROUP BY survey.si_sys_id

就像我说的，它以前工作过。我尝试更改日期以查看是否是 2021 年的问题，但没有帮助。我确认数据库有 Q4 和 Q5 数据并且它不应该为 NULL。有谁知道这里发生了什么？任何帮助，将不胜感激。如果您需要更多信息或有任何疑问，请不要犹豫。

标签： sqlsql-server

解决方案

您在 . 中缺少一个逗号and metric.name in ('Q1','Q2','Q3','Q4''Q5','Q6')。没有指标名称等于文字字符串Q4''Q5

推荐阅读