sql - 根据日期添加值

Question

我有下表，如果项目在同一个月内多次开具发票，我希望为项目和客户添加 Quantity_Sold 值。我想在单独的列中获得每月销售数量的总和

Item   Customer         Invoice_Date         Quantity_Sold
 A        XX      2014-11-04 00:00:00.000         13
 A        XX      2014-11-21 00:00:00.000         23
 A        XX      2014-12-19 00:00:00.000        209
 A        YY      2014-12-01 00:00:00.000         10
 A        YY      2014-12-22 00:00:00.000          6
 B        XX      2014-10-29 00:00:00.000        108
 B        YY      2014-11-06 00:00:00.000         70
 B        YY      2014-11-24 00:00:00.000         84

例如:XX 在 11 月两次为项目 A 开具发票，所以我想在单独的列中获得 36 (13+23)。

所以我想要的结果表是，

Item   Customer          Invoice_date         Sum_Qty_Invoiced
A         XX               2014-Nov                  36
A         XX               2014-Dec                 209
A         YY               2014-Dec                  16
B         XX               2014-Oct                 108   
B         YY               2014-Nov                 154 

如果有人可以帮助我，那就太好了
谢谢

Answer 1

这是一个简单的 group by，对 Invoice_Date 进行了一些字符串操作。柱子。

SELECT
  Item,
  Customer,
  CAST(Year(Invoice_Date) AS VARCHAR(4)) + '-' + LEFT(DateName(m,Invoice_Date),3) AS Invoice_Date,
  SUM(Quantity_Sold) AS Sum_Qty_Sold
FROM MyTable
GROUP BY Item,
  Customer,
  CAST(Year(Invoice_Date) AS VARCHAR(4)) + '-' + LEFT(Datename(m,Invoice_Date),3)

现场示例: http://www.sqlfiddle.com/#!6/8fea75/3