时间戳

首页 > 解决方案 > SQL中的除法 - 查找每日金额

sql - SQL中的除法 - 查找每日金额

问题描述

我有两张表，我需要在其中找到每日预算。

在我的表 [sdk$G_L Budget Entry] 中，我找到了每月预算。在我的表 [Salesboard Work Days] 中，我发现了一个月有多少个工作日。

现在我需要找出每天的每日预算。为此，我需要使用 MonthBudget 并除以工作日。

但我不确定，我是怎么做到的。我可以这样做，但认为这不是一个很好的方法。

(SELECT  CAST(ISNULL(ABS(SUM(Amount)),0) AS int) as MonthBudget FROM  [sdk$G_L Budget Entry] b  WHERE (MONTH(wd.Date) = MONTH(b.Date) AND YEAR(wd.date) = YEAR(b.Date)) AND b.[Budget Name] = '2020C' AND b.[G_L Account No_] LIKE '3%') / (SELECT SUM(w.WorkingDay) as WD FROM [Salesboard Working Days] w WHERE  MONTH(wd.Date) = MONTH(w.Date) AND YEAR(wd.date) = YEAR(w.Date) AND wd.WorkingDay = 1 ) AS DailyBudget

我的 SQL：

    SELECT 
            
        wd.[Date]
        ,wd.[WorkingDay]
        ,wd.[SalesPersonCode]
        ,wd.[companycode]
        ,(SELECT SUM(w.WorkingDay) as WD FROM [Salesboard Working Days] w WHERE  MONTH(wd.Date) = MONTH(w.Date) AND YEAR(wd.date) = YEAR(w.Date) AND wd.WorkingDay = 1 ) AS WorkingDays
        ,(SELECT  CAST(ISNULL(ABS(SUM(Amount)),0) AS int) as MonthBudget FROM  [sdk$G_L Budget Entry] b  WHERE (MONTH(wd.Date) = MONTH(b.Date) AND YEAR(wd.date) = YEAR(b.Date)) AND b.[Budget Name] = '2020C' AND b.[G_L Account No_] LIKE '3%') AS MonthBudget
        ,'??' As DailyBudget
      
    FROM 
      
        [Salesboard Working Days] wd 
      
    WHERE 
      
        wd.WorkingDay = 1 AND wd.SalesPersonCode IS NULL AND wd.companycode = 'sdk'  AND wd.[Date] >= '2020-07-27' AND wd.[Date] <= '2020-08-07'
    
        ORDER BY wd.Date

结果：

Date    WorkingDay  SalesPersonCode companycode WorkingDays MonthBudget DailyBudget
2020-07-27 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-28 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-29 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-30 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-07-31 00:00:00.000 1   NULL    sdk 23  2344594  ??
2020-08-03 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-04 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-05 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-06 00:00:00.000 1   NULL    sdk 21  2689775  ??
2020-08-07 00:00:00.000 1   NULL    sdk 21  2689775  ??

标签： sqlsql-server

解决方案

您可以使用窗口函数和重写查询APPLY：

SELECT wd.*,
       SUM(wd.WorkingDay) OVER (PARTITION BY YEAR(wd.date), MONTH(wd.date)) as  WorkingDays,
       b.MonthBudget,
       (b.MonthBudget /
        SUM(w.WorkingDay) OVER (PARTITION BY YEAR(wd.date), MONTH(wd.date))
       ) as DalyBudget
FROM [Salesboard Working Days] wd OUTER APPLY
     (SELECT COALESCE(ABS(SUM(Amount)), 0) as MonthBudget
      FROM [sdk$G_L Budget Entry] b
      WHERE MONTH(wd.Date) = MONTH(b.Date) AND
            YEAR(wd.date) = YEAR(b.Date) AND
            b.[Budget Name] = '2020C' AND
            b.[G_L Account No_] LIKE '3%'
     ) b
WHERE wd.WorkingDay = 1 AND
      wd.SalesPersonCode IS NULL AND
      wd.companycode = 'sdk' AND
      wd.[Date] >= '2020-07-27' AND wd.[Date] <= '2020-08-07'    
ORDER BY wd.Date;

窗口函数简化了当月工作日的计算。APPLY实现横向连接，类似于子句中的相关子查询（FROM除了它可以返回多行和多列）。

推荐阅读