SQL查询以计算DATEDIFF的AVG

Question

我正试图编写一个查询，该查询为我提供Datediff的AVG

我有一个类似的问题，我正试图用我的新产品复制，它在这里：

SELECT DATENAME(MONTH, d.OPENED) AS MonthValue, 
SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN 
1 ELSE 0 END) AS SmallCommercialIndust, 
SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN 1 
ELSE 0 END) AS Residential
FROM hb_Disputes d
WHERE YEAR(d.OPENED) = YEAR(GETDATE())
GROUP BY DATENAME(MONTH, d.OPENED) 
ORDER BY MIN(d.OPENED);

这是我的输出：

Month Value  Small Commercial Indust     Residential
----------------------------------------------------
January             0                         0
February            0                         0
March               1                         0
April               0                         2
May                 0                         1
June                0                         1
July                1                         0
August              1                         0
September           0                         1
October             1                         0
November            1                         0
December            0                         1

以下是我试图创建的Query，它采用相同的信息，但添加了一个额外的组件，它采用d.OPENS并将其减去d.DATERLVD，然后为我提供当月的AVG：

SELECT DATENAME(MONTH, d.OPENED) AS MonthValue, 
SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN 1 
ELSE 0 END AND AVG(DATEDIFF(day, d.OPENED, d.DATERLVD)) AS 
SmallCommercialIndust, 
SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2  THEN 1 
ELSE 0 END AND AVG(DATEDIFF(day, d.OPENED, d.DATERLVD)) AS 
Residential
FROM hb_Disputes d
WHERE YEAR(d.OPENED) = YEAR(GETDATE())
GROUP BY DATENAME(MONTH, d.OPENED)
ORDER BY MIN(d.OPENED);

Answer 1

为什么在SELECT子句中使用AND？我想你想要：

SELECT DATENAME(MONTH, d.OPENED) AS MonthValue, 
SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN 1 ELSE 0 END) as SmallCommercialIndust,
AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN DATEDIFF(day, d.OPENED, d.DATERLVD) END) AS SmallCommercialIndust_avg, 
SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN 1 ELSE 0 END) as Residential,
AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN DATEDIFF(day, d.OPENED, d.DATERLVD) END) AS Residential_avg
FROM hb_Disputes d

Answer 2

了解如何正确格式化代码。你可以清楚地看到你把括号放错了地方。

SELECT DATENAME(MONTH, d.OPENED) AS MonthValue
, SUM(
CASE 
WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN 1 
ELSE 0 
END 
AND 
AVG(DATEDIFF(day, d.OPENED, d.DATERLVD)
) AS SmallCommercialIndust
, SUM(
CASE 
WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2  THEN 1 
ELSE 0 
END
AND AVG(DATEDIFF(day, d.OPENED, d.DATERLVD)
) AS Residential
FROM hb_Disputes d
WHERE YEAR(d.OPENED) = YEAR(GETDATE())
GROUP BY DATENAME(MONTH, d.OPENED)
ORDER BY MIN(d.OPENED);