如何根据一个月的工作天数计算金额 [英] How to calculate amount based on the number of working days from the month

问题描述

我有以下查询，该查询显示具有日期(月)和金额的表:

I have the following query which displays a table with date (month) and an amount:

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

显示以下内容:

date    amount
201304  1750359.95
201305  1853203.29
201306  1741522.66
201307  1655812.14

我有以下查询，该查询给出了该月的工作(工作)天数:

I have the following query which gives how many workings (business) days are in this month:

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

如何结合我的第一个查询和第二个查询(从月中获取工作日)以显示我的表，如下所示:

How can I combine my first query and the second query (get the working days from the month) to display my table as following:

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  …

平均值应为金额除以该月的工作天数.

The average should be the the amount divided by the number of working days for that month.

如何获得平均值?

推荐答案

请尝试以下操作:

   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]

这篇关于如何根据一个月的工作天数计算金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！