SQL求日期差平均值的精确十进制值问题求助

Hey there! I totally get how frustrating it is when your SQL query result doesn’t line up with your manual Excel calculation—especially after you’ve already dug into past questions and tested multiple fixes. Since you’re still learning, let’s walk through some common spots where this discrepancy might be happening, step by step.

First, let’s recap your query for clarity:

SELECT AVG(DATEDIFF(DAY, xx, yy)) AS DayDiff 
FROM database1.dbo.table1 
WHERE MONTH(datecompleted) = MONTH(DATEADD(MONTH,-1,CURRENT_TIMESTAMP)) 
  AND YEAR(datecompleted) = YEAR(DATEADD(MONTH,-1,CURRENT_TIMESTAMP)) 
  AND ApprovalRequiredFrom = 'GRM'

You’re getting a result of 9 in SQL, but your Excel manual calculation doesn’t match. Here are things to check:

• Check for NULL values in your date columns
  SQL’s AVG() function automatically skips any rows where xx or yy is NULL. If your Excel calculation included rows with missing dates (even if they look empty), that would throw off the average. Run this quick query to see if there are any NULLs in your target rows:

  SELECT COUNT(*) AS NullDateRows
  FROM database1.dbo.table1 
  WHERE (xx IS NULL OR yy IS NULL)
    AND MONTH(datecompleted) = MONTH(DATEADD(MONTH,-1,CURRENT_TIMESTAMP)) 
    AND YEAR(datecompleted) = YEAR(DATEADD(MONTH,-1,CURRENT_TIMESTAMP)) 
    AND ApprovalRequiredFrom = 'GRM'
  

  If this returns a number greater than 0, those rows are excluded from SQL’s average but might have been included in Excel (even as 0 or blank, which AVERAGE() ignores—but if you summed manually, you might have counted them).

• Verify your date filter is capturing the exact right rows
  Using MONTH() and YEAR() works most of the time, but sometimes edge cases (like if datecompleted has a time component, though that shouldn’t affect month/year) or date formatting quirks can lead to unexpected results. Try rewriting your filter to be more explicit—it’s a safer way to target last month’s data:

  SELECT AVG(DATEDIFF(DAY, xx, yy)) AS DayDiff 
  FROM database1.dbo.table1 
  WHERE datecompleted >= DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP)-1, 1)
    AND datecompleted < DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 1)
    AND ApprovalRequiredFrom = 'GRM'
  

  This ensures you’re grabbing every date from the first day of last month up to (but not including) the first day of this month, no exceptions.

• Compare raw data sets side by side
  The best way to spot differences is to pull all the individual day differences from SQL and paste them into Excel. Run this query to get the raw values:

  SELECT DATEDIFF(DAY, xx, yy) AS IndividualDayDiff 
  FROM database1.dbo.table1 
  WHERE MONTH(datecompleted) = MONTH(DATEADD(MONTH,-1,CURRENT_TIMESTAMP)) 
    AND YEAR(datecompleted) = YEAR(DATEADD(MONTH,-1,CURRENT_TIMESTAMP)) 
    AND ApprovalRequiredFrom = 'GRM'
  

  Then calculate the average of these numbers in Excel using AVERAGE(). If the average still doesn’t match, you can scan the list for outliers, missing rows, or extra rows that aren’t in your Excel data set.

• Double-check Excel’s calculation logic
  Make sure you’re using Excel’s AVERAGE() function (which ignores blank cells, just like SQL’s AVG()). If you manually summed all values and divided by the number of rows, you might have included blank cells or counted rows that SQL excluded. Also, confirm that your Excel data filter for datecompleted and ApprovalRequiredFrom = 'GRM' is exactly the same as your SQL query—even a tiny difference (like including the first day of this month by mistake) can change the average.

Let me know if any of these steps help you find the mismatch! It’s usually something small, so breaking it down like this should point you in the right direction.

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