时间戳

首页 > 解决方案 > 查询计算每日预算

sql - 查询计算每日预算

问题描述

我有这个 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

结果

标签： sqlsql-server

解决方案

您可以使用运算符划分它们/。我会将原始查询包装在子查询中，这样您就不必重复工作日计算：

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

推荐阅读