SQL Query to calculate the AVG of DATEDIFF

I’m trying to write a query that gives me a gives me the AVG of a Datediff

I have a similar query that I am trying to replicate with my new and here it is:

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);

Here is my output:

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

Below is the Query I am attempting to create, which takes the same information but adds an additional component where it takes d.OPENS and subtract it to d.DATERLVD and provide me with the AVG for the month:

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

Why are you using AND in the SELECT clause? I think you want:

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

Advertisement

Answer