查询以计算每日预算

Question

我有这个SQL，我可以在其中获得每月预算和一个月的工作日数。但我需要每日预算的号码

Monthbudget / Workingdays

我该如何解决？

SELECT     
b.Date, 
CAST(ISNULL(ABS(SUM(Amount)),0) AS int) AS MonthBudgetAmount,
(SELECT COUNT(wd.WorkingDay) as WD FROM [Salesboard Working Days] wd WHERE (MONTH(wd.Date) = MONTH(b.Date) AND YEAR(wd.date) = YEAR(b.Date)) AND wd.WorkingDay = 1) AS WorkingDays,
'0' As DailyBudgetAmount    
FROM [Selek$G_L Budget Entry] b
WHERE b.[Budget Name] = '2020C' AND b.[G_L Account No_] LIKE '3%'
GROUP BY b.Date

结果

Answer 1

您可以使用/运算符对它们进行除法。我会将原始查询封装在子查询中，这样您就不必重复工作日的计算：

SELECT (MonthBudgetAmount, 
WorkingDays, 
CASE WorkingDays WHEN 0 THEN -1 -- Or some other default to avoid zero division
ELSE MonthBudgetAmount / WorkingDays
END
FROM    (SELECT   b.Date, 
CAST(ISNULL(ABS(SUM(Amount)),0) AS int) AS MonthBudgetAmount,
(SELECT COUNT(wd.WorkingDay) as WD FROM [Salesboard Working Days] wd WHERE (MONTH(wd.Date) = MONTH(b.Date) AND YEAR(wd.date) = YEAR(b.Date)) AND wd.WorkingDay = 1 
)  AS WorkingDays
FROM     [Selek$G_L Budget Entry] b
WHERE    b.[Budget Name] = '2020C' AND b.[G_L Account No_] LIKE '3%'
GROUP BY b.Date) t

Answer 2

WITH BudgetWorkingDays (BudgetDate,MonthBudgetAmount, WorkingDays)
AS (SELECT b.Date, 
CAST(ISNULL(ABS(SUM(Amount)),0) AS int) AS MonthBudgetAmount,
(SELECT COUNT(wd.WorkingDay) as WD 
FROM [Salesboard Working Days] wd 
WHERE (MONTH(wd.Date) = MONTH(b.Date) 
AND YEAR(wd.date) = YEAR(b.Date)) 
AND wd.WorkingDay = 1)  AS WorkingDays
FROM [Selek$G_L Budget Entry] b
WHERE b.[Budget Name] = '2020C' AND b.[G_L Account No_] LIKE '3%'
GROUP BY b.Date                 
)
SELECT BudgetDate, MonthBudgetAmount, WorkingDays, 
MonthBudgetAmount / WorkingDays AS DailyBudget 
FROM BudgetWorkingDays