如何根据当月的工作天数计算金额

Question

我有以下查询，它显示了一个包含日期（月）和金额的表：

SELECT TOP 1000 [date]
      ,[amount]
  FROM [database].[dbo].[table]

显示以下内容：

date    amount
201304  1750359.95
201305  1853203.29
201306  1741522.66
201307  1655812.14

我有以下查询，给出了本月有多少工作（营业）日：

DECLARE @theDate DATETIME
SET @theDate = GETDATE()
SELECT 20 + COUNT(*) FROM 
   ( SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 28) AS theDate 
     UNION 
     SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 29) 
     UNION 
     SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 30) ) AS d 
WHERE DATEPART(DAY, theDate) > 28 AND DATEDIFF(DAY, 0, theDate) % 7 < 5

我如何将我的第一个查询和第二个查询（从一个月中获取工作日）组合起来显示我的表如下：

date    amount      average
201304  1750359.95  1750359.95/22 = 79561.81
201305  1853203.29  1853203.29/23 = 80574.05
201306  1741522.66  …
201307  1655812.14  …

平均值应该是金额除以当月的工作天数。

我怎样才能得到平均值？

Answer 1

请尝试这个：

   SELECT TOP 1000 [date]
          ,[amount], [amount]/(SELECT 20 + COUNT(*) FROM 
         (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 28) AS theDate 
         UNION 
         SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 29) 
         UNION 
         SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 30) ) AS d 
    WHERE DATEPART(DAY, [date]) > 28 AND DATEDIFF(DAY, 0, [date]) % 7 < 5)
    FROM [database].[dbo].[table]

Answer 2

您可以尝试这个sqlfiddle演示，演示我为计算每个月的周天数所做的工作。显然，如果你包括假期，这是行不通的，如果你需要包括假期，我建议创建一个日历查找表，这样就不会变得过于复杂。

以下示例的代码：

select top 1000 [date], [amount]
datediff(dd, DATEADD(mm, DATEDIFF(m,0,[date]), 0), dateadd(d, -1, DATEADD(m, 1,DATEADD(mm, DATEDIFF(m,0,[date]), 0)))) 
  - (datediff(wk, DATEADD(mm, DATEDIFF(m,0,[date]), 0), dateadd(d, -1, DATEADD(m, 1,DATEADD(mm, DATEDIFF(m,0,[date]), 0)))) * 2) -
    case when datepart(dw, DATEADD(mm, DATEDIFF(m,0,[date]), 0)) = 1 
         then 1 else 0 end +
    case when datepart(dw, dateadd(d, -1, DATEADD(m, 1,DATEADD(mm, DATEDIFF(m,0,[date]), 0)))) = 1 
         then 1 else 0 end + 1
from [database].[dbo].[table]