Блог

У меня есть этот 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
  

Результат

Вы можете разделить их с помощью / оператора. Я бы заключил исходный запрос в подзапрос, чтобы вам не приходилось повторять расчет рабочих дней:

 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
  

 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