时间戳

首页 > 解决方案 > T-SQL：三天内两个日期之间的差异百分比

sql - T-SQL：三天内两个日期之间的差异百分比

问题描述

我正在尝试计算两个日期之间的百分比差异，这是一个目前完美运行的示例：

SELECT (SELECT COUNT(mp.RETURN_INTERVIEW_ACCEPTED) * 100.00 
        FROM Child_Social.FACT_MISSING_PERSON AS mp
        WHERE mp.RETURN_INTERVIEW_ACCEPTED LIKE 'Y'
        AND CAST (mp.END_DTTM AS DATE) 
        BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE))
        /
        (SELECT COUNT(mp.RETURN_INTERVIEW_OFFERED)
        FROM Child_Social.FACT_MISSING_PERSON AS mp
        WHERE mp.RETURN_INTERVIEW_OFFERED LIKE 'Y'
        AND CAST (mp.END_DTTM AS DATE) 
        BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE))
--Subquery to determine % between # of interviews offered vs # of interviews accepted
    FROM Child_Social.FACT_MISSING_PERSON AS mp
    WHERE CAST (mp.END_DTTM AS DATE) 
        BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE)

在上面，我将一年内接受的所有面试除以当年提供的面试数量。使用查询后面的数字，这为我生成了 58%：

SELECT 426 * 100.00 / 725

但是，在稍微不同的查询上使用相同的原则，我没有得到预期的数字。我期待看到这个，它产生 50.90%：

SELECT 253 * 100.00 / 497

但我一直得到“1”？下面查询的最大区别是我试图确定在结束日期 (END_DTTM) 的三天内进行了多少次采访的百分比，因此 <=3 子句，我想这是导致问题的原因

SELECT COUNT(mp.RETURN_INTERVIEW_DATE) * 100  /
    (SELECT COUNT(mp.RETURN_INTERVIEW_DATE) 
        FROM Child_Social.FACT_MISSING_PERSON AS mp
        WHERE CAST (mp.END_DTTM AS DATE) 
        BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE) AND
        (DATEDIFF(DAY, mp.END_DTTM, mp.RETURN_INTERVIEW_DATE)) <=3) 
    FROM Child_Social.FACT_MISSING_PERSON AS mp
    WHERE CAST (mp.END_DTTM AS DATE) 
        BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE)

我有点难过，我尝试了一些不同的东西，但我无法让它工作，任何帮助将不胜感激！

谢谢

标签： sqlsql-serverssms

解决方案

在您的第二个查询中，您正在执行所有整数数学运算，因为您乘以100而不是100.00在第一个查询中。

100.00如果在第二个查询中乘以，您会得到什么答案？

另外，如果你分别选择分子和分母，你会得到预期的数字吗？

SELECT COUNT(mp.RETURN_INTERVIEW_DATE), /* * 100 */
    (SELECT COUNT(mp.RETURN_INTERVIEW_DATE) 
        FROM Child_Social.FACT_MISSING_PERSON AS mp
        WHERE CAST (mp.END_DTTM AS DATE) 
        BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE) AND
        (DATEDIFF(DAY, mp.END_DTTM, mp.RETURN_INTERVIEW_DATE)) <=3) 
    FROM Child_Social.FACT_MISSING_PERSON AS mp
    WHERE CAST (mp.END_DTTM AS DATE) 
        BETWEEN DATEADD(YY, -1, CAST (GETDATE() AS DATE)) AND CAST (GETDATE() AS DATE)

推荐阅读