MTD和YTD of Sales SQL

Question

我有一张叫Sales_History的桌子

我需要做的是为某些销售人员填充一个YTD和一个MTD。

我必须要玩的领域是：

Salesperson_Invoiced (需要按) NetNet_Revenue_Func (需要MTD和YTD图) GM_Func_Net (需要MTD和YTD图)在where子句中使用的其他字段

我第一个发怒的地方是：

Select t.Salesperson_Invoiced,
  Sum(y.NetNet_Revenue_Func) YTD_REV,
  Sum(m.NetNet_Revenue_Func) MTD_REV
  From Sales_History t
   join Sales_History y
      on y.Salesperson_Invoiced = t.Salesperson_Invoiced
         and datediff(year, y.TranDate, t.TranDate) = 0 
         and y.TranDate <= t.TranDate
   join Sales_History m
      on m.Salesperson_Invoiced = t.Salesperson_Invoiced
         and datediff(month, m.TranDate, t.TranDate) = 0 
         and m.TranDate <= t.TranDate
         where t.PG1 = 'Lighting' and t.Office = 'AU' and t.Year = '2021'
Group by t.Salesperson_Invoiced, t.TranDate

但这并没有给我想要的：

我想看到的是：

+----------------------+-------------------------+-------------------------+-----------------+-----------------+
| Salesperson_Invoiced | NetNet_Revenue_Func MTD | NetNet_Revenue_Func YTD | GM_Func_Net MTD | GM_Func_Net YTD |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+
| James                | 500                     | 600                     |                 |                 |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+
| John                 | 600                     | 700                     |                 |                 |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+
| Peter                | 700                     | 800                     |                 |                 |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+
| Harry                | 800                     | 900                     |                 |                 |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+
| Potter               | 900                     | 1000                    |                 |                 |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+

我知道上面的代码只有NetNet_Revenue_Func认为我会先得到一个工作，然后再添加另一个。

如果有人能让我知道我做错了什么，或者告诉我如果我做错了，那就太好了！)

大家好，

由于Squirel，我已经对代码做了一些修改如下：

这是否是我正在做的事情最好的方式:)？

Select  t.Salesperson_Invoiced,
        SUM (Case When t.Year = Year(getdate()) Then t.NetNet_Revenue_Func End) YTD_REV,
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.NetNet_Revenue_Func 
                 End) MTD_REV
From    Sales_History t
Where   t.PG1    = 'Lighting' 
And     t.Office = 'AU' 
And     t.Year   = Year(getdate())
Group By t.Salesperson_Invoiced

更新-

下面是一个工作的SQL

Select  t.Salesperson_Invoiced,        
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.NetNet_Revenue_Func End) MTD_REV,
        Sum(Case When month(t.TranDate) = Month(getdate()) Then t.GM_Func_Net End) MTD_GM,
        SUM (Case When t.Year = Year(getdate()) Then t.NetNet_Revenue_Func End) YTD_REV,
                SUM (Case When t.Year = Year(getdate()) Then t.GM_Func_Net End) YTD_GM
From    Sales_History t
Where   t.PG1    = 'Lighting' 
And     t.Office = 'AU' 
And     t.Year   = Year(getdate())
Group By t.Salesperson_Invoiced

我可以添加以下内容吗？

我可以添加以下内容吗？

SELECT Salesperson_1,sum(Value_Func) as BO_AUD
FROM Datawarehouse.dbo.Open_Orders
where Office = 'AU' and PG1 = 'Lighting'
group by Salesperson_1

这样我就能让它看起来像这样？

+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+
| Salesperson_Invoiced | NetNet_Revenue_Func MTD | NetNet_Revenue_Func YTD | GM_Func_Net MTD | GM_Func_Net YTD | BO_AUD |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+
| James                | 500                     | 100                     |                 |                 |        |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+
| John                 | 600                     | 200                     |                 |                 |        |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+
| Peter                | 700                     | 300                     |                 |                 |        |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+
| Harry                | 800                     | 400                     |                 |                 |        |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+
| Potter               | 900                     | 1                       |                 |                 |        |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+

Answer 1

如果我正确理解了您的需求，您可以将查询简化到下面。

您不需要多次JOIN Sales_History。可以使用CASE表达式执行条件SUM()以获取MTD值。假设MTD是指当月

Select  t.Salesperson_Invoiced,
        Sum(t.NetNet_Revenue_Func) YTD_REV,
        Sum(Case When month(t.TransDate) = Month(getdate()) 
                 Then t.NetNet_Revenue_Func 
                 End) MTD_REV
From    Sales_History t
Where   t.PG1    = 'Lighting' 
And     t.Office = 'AU' 
And     t.Year   = 2021
Group By t.Salesperson_Invoiced

对于YTD计算，可以使用下面的表达式。

SUM (Case When t.Year = Year(getdate()) 
          Then t.NetNet_Revenue_Func 
          End) YTD_REV

由于表中有Year列，所以我使用它。否则你也可以

SUM (Case When Year(t.TransDate) = Year(getdate()) 
          Then t.NetNet_Revenue_Func 
          End) YTD_REV

无论如何，出于性能原因，您还应该在WHERE中筛选所需的时间段，就像您现在使用t.Year = 2021所做的一样。

编辑:还修正了先前查询中的表别名错误。注意:表中的Year列应该是int，对吗？所以您不需要在And t.Year = 2021年使用单引号