时间戳

首页 > 解决方案 > 销售 SQL 的 MTD 和 YTD

sql-server-2014 - 销售 SQL 的 MTD 和 YTD

问题描述

我有一张名为 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 认为我会先得到一个 workign，然后再添加另一个。

如果有人可以请让我知道我做错了什么，或者告诉我如果我把这一切都错了，那就太好了！:)

大家好，

感谢 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                       |                 |                 |        |
+----------------------+-------------------------+-------------------------+-----------------+-----------------+--------+

标签： sql-server-2014

解决方案

如果我正确理解您的要求，您可以将查询简化为以下内容。

你不需要多次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

推荐阅读